You are working with PostgreSQL tables that store account registrations and login events.
The relevant schemas are:
users contains user_id (integer primary key) and signup_date (date).logins contains login_id (integer), user_id (integer), browser (variable-length text up to 20 characters), and login_date (date). Each row represents one login event.user_id_rehash is used only in Part 3. It contains old_user_id (integer), new_user_id (integer), and rehash_date (date). It indicates that from rehash_date onward, a previously seen real user identified by old_user_id appears in logins under new_user_id.Write PostgreSQL queries or answers for each part below.
For every calendar day between 2025-05-25 and 2025-06-01, inclusive, produce a row containing:
activity_datedau, defined as the number of distinct user_id values whose login_date equals that date.Missing dates are not acceptable: every day in the interval must appear. If no login occurred on a day, dau must be 0.
Order the rows by activity_date in ascending order.
Follow-up: if no prebuilt calendar table exists, explain how you would generate the required date dimension in PostgreSQL.
For the same interval 2025-05-25 through 2025-06-01, inclusive, compute a rolling 30-day MAU for each calendar day.
For a given activity_date, the lookback window covers the 30 calendar days ending on that date: from 29 days before activity_date through activity_date, inclusive. Define mau_30d as the number of distinct user_id values that logged in at least once during that window. Each user is counted at most once per activity_date, even if they logged in on many days inside the window.
The result must contain columns activity_date and mau_30d.
Order the rows by activity_date in ascending order. Every date in the interval must appear, including dates with no login on that specific day.
Follow-up: if the product is mostly used on weekdays and has very little weekend activity, what issues may arise with this calendar 30-day window? What alternative definitions could be more appropriate?
For every calendar day from 2025-05-20 through 2025-06-01, inclusive, compute two rolling 30-day metrics:
naive_mau_30d: count distinct logins.user_id values in the 30-day window ending on the date.corrected_mau_30d: first map each logins.user_id that appears as new_user_id in user_id_rehash back to its corresponding old_user_id, then count distinct canonical user IDs.For users whose IDs do not appear as new_user_id in user_id_rehash, their existing user_id remains unchanged.
The overestimate percentage for a date is:
Return a single row with these columns:
max_overestimate_pctmin_overestimate_pctBoth values must be rounded to two decimal places. You may assume corrected_mau_30d is never zero for any date in this interval.
Example 1:
Input:
users rows:
(1, 2025-05-20)
(2, 2025-05-21)
(3, 2025-05-21)
(4, 2025-05-01)
logins rows:
(101, 1, 'Chrome', 2025-05-25)
(102, 2, 'Safari', 2025-05-25)
(103, 1, 'Chrome', 2025-05-25)
(104, 3, 'Firefox', 2025-05-27)
(105, 4, 'Edge', 2025-05-10)
Query: Part 1 over 2025-05-25 to 2025-06-01
Output:
activity_date | dau
2025-05-25 | 2
2025-05-26 | 0
2025-05-27 | 1
2025-05-28 | 0
2025-05-29 | 0
2025-05-30 | 0
2025-05-31 | 0
2025-06-01 | 0
Explanation: User 1 logs in twice on May 25 but is counted once, so that day has DAU 2.
Example 2:
Input:
Same users and logins rows as Example 1.
Query: Part 2 over 2025-05-25 to 2025-06-01
Output:
activity_date | mau_30d
2025-05-25 | 3
2025-05-26 | 3
2025-05-27 | 4
2025-05-28 | 4
2025-05-29 | 4
2025-05-30 | 4
2025-05-31 | 4
2025-06-01 | 4
Explanation: On May 25, the 30-day window includes user 4's May 10 login and users 1 and 2 from May 25; user 3 enters the window on May 27.
Example 3:
Input:
user_id_rehash rows:
(100, 200, 2025-05-28)
logins rows:
(201, 300, 'Chrome', 2025-05-20)
(202, 100, 'Chrome', 2025-05-22)
(203, 200, 'Chrome', 2025-05-29)
Query: Part 3 over 2025-05-20 to 2025-06-01
Output:
max_overestimate_pct | min_overestimate_pct
50.00 | 0.00
Explanation: From May 29 onward, the window contains both old ID 100 and its new ID 200, so the naive distinct count is inflated until correction maps them back to the same canonical user.
Constraints:
DATE values, and intervals are inclusive on both endpoints.2025-05-25 through 2025-06-01.2025-05-20 through 2025-06-01.corrected_mau_30d is positive for every evaluated date.activity_date ascending.CREATE TABLE users (
user_id INT PRIMARY KEY,
signup_date DATE
);
CREATE TABLE logins (
login_id INT,
user_id INT,
browser VARCHAR(20),
login_date DATE
);
CREATE TABLE user_id_rehash (
old_user_id INT,
new_user_id INT,
rehash_date DATE
);