cswork / libraryOpen practice workspace

Coding questions · Meta

Define and analyze new-vs-existing activity

This question evaluates a candidate's competency in cohort definition, time-series event aggregation, use of window functions, cohort-level weighting and bias-aware analytics using SQL and Python.

Ambiguous product question: Are existing users more active than new users over the last 28 days (ending today = 2025-09-01)? 1) Propose two reasonable, mutually exclusive definitions for existing vs new (e.g., by signup date or by prior activity), and two defensible definitions of active (e.g., DAU, sessions/week). Briefly state pros/cons and pick one pair to implement. 2) Using the schema and sample data below, write SQL that: a) labels users as new or existing; b) computes each cohort's 7-day rolling active rate and average daily events/user over the last 28 days; c) adjusts for partial observation windows for users who signed up within the window; d) produces a final table with date, cohort, active_users, total_users_observed, active_rate_7d, avg_events_per_user. Use window functions (e.g., partition by user, rolling windows) and avoid double-counting users across cohorts. 3) Extend your query to stratify by country and then produce a cohort-level weighted average controlling for country mix. 4) Briefly note two bias risks (e.g., survivorship, seasonality) and one SQL-side mitigation you implemented. Schema (you may add a small date calendar CTE if needed):

users(user_id INT, signup_date DATE, country STRING)
events(user_id INT, event_date DATE, event_type STRING)
Sample rows:
users
user_id | signup_date | country
1 | 2025-08-15 | US
2 | 2025-06-10 | US
3 | 2025-08-30 | CA
4 | 2025-07-01 | IN
5 | 2025-08-20 | US

events
user_id | event_date | event_type
1 | 2025-08-29 | view
1 | 2025-09-01 | message
2 | 2025-08-25 | like
2 | 2025-08-31 | view
3 | 2025-09-01 | view
4 | 2025-08-28 | view
5 | 2025-08-31 | comment

New vs Existing Cohorts: 7-day Rolling Active Rate and Daily Events per User

Assume today is 2025-09-01. Analyze the 28-day period FROM 2025-08-05 TO 2025-09-01 (inclusive).

Use the following mutually exclusive cohort definition:

  • 'existing' users: signup_date < 2025-08-05
  • 'new' users: signup_date BETWEEN 2025-08-05 AND 2025-09-01

Define a user as "active" on a given date if they generated at least one event on that date.

Write a single SQL query (you may use CTEs, including a date calendar CTE) that produces a daily cohort-level table with the following columns:

  • date
  • cohort (existing/new)
  • active_users: number of active users on that date (distinct users)
  • total_users_observed: number of users in the cohort who have signed up on or before that date (to adjust for partial observation windows)
  • active_rate_7d: 7-day rolling active rate = (number of users with >=1 event in the 7-day window ending on date) / total_users_observed
  • avg_events_per_user: total events on that date / total_users_observed

Requirements:

  • Use window functions to compute the 7-day rolling activity per user.
  • Avoid double-counting users across cohorts.
  • Output one row per date per cohort for all dates 2025-08-05 through 2025-09-01.
  • For cohort-date combinations with total_users_observed = 0, return NULL for rate/average metrics (avoid divide-by-zero).

Table users

columntypeconstraints
user_idINTPRIMARY KEY, NOT NULL
signup_dateDATENOT NULL
countryVARCHAR(2)NOT NULL

Sample data:

countrysignup_dateuser_id
US2025-08-151
US2025-06-102
CA2025-08-303
IN2025-07-014
US2025-08-205
IN2025-08-106
CA2025-05-207

Table events

columntypeconstraints
user_idINTNOT NULL
event_dateDATENOT NULL
event_typeVARCHAR(20)NOT NULL

Sample data:

event_dateevent_typeuser_id
2025-08-29view1
2025-09-01message1
2025-08-25like2
2025-08-31view2
2025-09-01view3
2025-08-28view4
2025-08-31comment5
2025-08-26view6
2025-08-27comment7

Expected output

One row per date (2025-08-05..2025-09-01) per cohort with daily active users, observed users, 7-day rolling active rate, and average daily events per observed user. Rates/averages are NULL when total_users_observed = 0.

active_rate_7dactive_usersavg_events_per_usercohortdatetotal_users_observed
000existing2025-08-053
NULL0NULLnew2025-08-050
000existing2025-08-063
NULL0NULLnew2025-08-060
000existing2025-08-073
NULL0NULLnew2025-08-070
000existing2025-08-083
NULL0NULLnew2025-08-080
000existing2025-08-093
NULL0NULLnew2025-08-090
000existing2025-08-103
000new2025-08-101
000existing2025-08-113
000new2025-08-111
000existing2025-08-123
000new2025-08-121
000existing2025-08-133
000new2025-08-131
000existing2025-08-143
000new2025-08-141
000existing2025-08-153
000new2025-08-152
000existing2025-08-163
000new2025-08-162
000existing2025-08-173
000new2025-08-172
000existing2025-08-183
000new2025-08-182
000existing2025-08-193
000new2025-08-192
000existing2025-08-203
000new2025-08-203
000existing2025-08-213
000new2025-08-213
000existing2025-08-223
000new2025-08-223
000existing2025-08-233
000new2025-08-233
000existing2025-08-243
000new2025-08-243
0.33333310.333333existing2025-08-253
000new2025-08-253
0.33333300existing2025-08-263
0.33333310.333333new2025-08-263
0.66666710.333333existing2025-08-273
0.33333300new2025-08-273
110.333333existing2025-08-283
0.33333300new2025-08-283
100existing2025-08-293
0.66666710.333333new2025-08-293
100existing2025-08-303
0.500new2025-08-304
110.333333existing2025-08-313
0.7510.25new2025-08-314
100existing2025-09-013
120.5new2025-09-014

Hints

  1. Create a calendar of dates (2025-08-05..2025-09-01), then build a user-date grid where date >= signup_date to handle partial observation.
  2. Compute a per-user daily active flag (0/1), then use a 7-row window (ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) to mark whether a user was active in the last 7 days.

Country-Standardized Cohort Metrics (Weighted by Overall Country Mix)

You are analyzing daily user activity for two signup cohorts and want to compare them while controlling for differences in country mix between the cohorts. There are two tables:

  • users — one row per user: user_id, signup_date, country (2-letter code).
  • events — one row per user action: user_id, event_date, event_type.

Cohort definition (assign every user to exactly one cohort by signup date):

  • existing — signup_date < 2025-08-05
  • new — signup_date BETWEEN 2025-08-05 AND 2025-09-01

Observation window. A user is observed on every calendar day from their signup_date through 2025-09-01 (a user contributes one row per (date, user) and must be counted only once per date). Build daily activity over the window 2025-08-05 through 2025-09-01.

Per-user-day metrics.

  • events_cnt = number of events that user generated on that date (0 if none).
  • A user is 7-day active on a date if they generated at least one event in the trailing 7-day window (the date itself plus the 6 preceding observed days). Compute this with a window function (ROWS BETWEEN 6 PRECEDING AND CURRENT ROW over each user's ordered days).

For report dates 2025-08-30, 2025-08-31, and 2025-09-01 (inclusive) produce country-standardized cohort metrics:

  1. Per (date, cohort, country) compute:
    • total_users_observed_country = number of observed users,
    • active_users_7d_country = number of 7-day-active users,
    • total_events_country = sum of events_cnt,
    • active_rate_7d_country = active_users_7d_country / total_users_observed_country,
    • avg_events_per_user_country = total_events_country / total_users_observed_country.
  2. Per (date, country) compute a weight from all observed users that date (both cohorts combined):
    • weight_country = total_observed_in_country / total_observed_all_countries.
  3. Final output, one row per (date, cohort):
    • standardized_active_rate_7d = SUM(active_rate_7d_country * weight_country) over that date's countries,
    • standardized_avg_events_per_user = SUM(avg_events_per_user_country * weight_country) over that date's countries.

Output columns (in this order): date, cohort, standardized_active_rate_7d, standardized_avg_events_per_user. Round both metrics to 6 decimal places. Sort by date ascending, then cohort with existing before new.

Table users

columntypeconstraints
user_idINTPRIMARY KEY, NOT NULL
signup_dateDATENOT NULL
countryVARCHAR(2)NOT NULL

Sample data:

countrysignup_dateuser_id
US2025-08-151
US2025-06-102
CA2025-08-303
IN2025-07-014
US2025-08-205
IN2025-08-106
CA2025-05-207

Table events

columntypeconstraints
user_idINTNOT NULL
event_dateDATENOT NULL
event_typeVARCHAR(20)NOT NULL

Sample data:

event_dateevent_typeuser_id
2025-08-29view1
2025-09-01message1
2025-08-25like2
2025-08-31view2
2025-09-01view3
2025-08-28view4
2025-08-31comment5
2025-08-26view6
2025-08-27comment7

Expected output

One row per report date (2025-08-30..2025-09-01) and cohort, giving the country-standardized 7-day active rate and the country-standardized average daily events per user, using that date's all-cohorts country weights.

cohortdatestandardized_active_rate_7dstandardized_avg_events_per_user
existing2025-08-301.0000000.000000
new2025-08-300.5000000.000000
existing2025-08-311.0000000.428571
new2025-08-310.7142860.214286
existing2025-09-011.0000000.000000
new2025-09-011.0000000.500000

Hints

  1. Build a per-(date,user) observation grid first (calendar CROSS/JOIN users from signup_date onward) so every user is counted exactly once per date.
  2. For the 7-day active flag, use SUM(is_active) OVER (PARTITION BY user_id ORDER BY activity_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) > 0.
  3. Country weights come from ALL observed users that day (both cohorts) — compute them in a separate per-(date,country) CTE, then join to the per-cohort rates and SUM(rate * weight).