cswork / libraryOpen practice workspace

Coding questions · Meta

Write SQL for visibility, calls, and cohort activity

This question evaluates a candidate's proficiency in SQL-based data manipulation, covering aggregations, joins, window functions, date arithmetic, deduplication, cohorting, and calculation of engagement metrics.

You have the following schema and toy data. Assume "today" = 2025-09-01.

users(user_id INT, signup_date DATE)
 Sample:
 user_id | signup_date
 --------+------------
 1 | 2025-08-31
 2 | 2025-08-15
 3 | 2025-06-01
 4 | 2025-09-01

shops(shop_id INT, category TEXT)
 Sample:
 shop_id | category
 --------+---------
 10 | Bakery
 11 | Grocery
 12 | Pharmacy

visibility_events(event_time TIMESTAMP, user_id INT, shop_id INT, position INT, visible BOOLEAN, dwell_seconds INT)
 Sample:
 event_time | user_id | shop_id | position | visible | dwell_seconds
 ---------------------+---------+---------+----------+---------+--------------
 2025-09-01 09:00:00 | 1 | 10 | 1 | true | 6
 2025-09-01 09:00:05 | 1 | 11 | 4 | true | 2
 2025-09-01 09:10:00 | 2 | 10 | 2 | true | 8
 2025-09-01 10:00:00 | 3 | 12 | 1 | true | 12
 2025-09-01 10:05:00 | 3 | 10 | 5 | false | 0
 2025-09-01 11:00:00 | 4 | 11 | 2 | true | 7

calls(started_at TIMESTAMP, caller_id INT, receiver_id INT)
 Sample:
 started_at | caller_id | receiver_id
 ---------------------+-----------+------------
 2025-09-01 08:00:00 | 1 | 2
 2025-09-01 09:30:00 | 3 | 1
 2025-08-31 22:00:00 | 2 | 4

Tasks (write SQL; be careful about edge cases like duplicates, invisible rows, and users with zero activity today): A) For 2025-09-01, return shop_id, unique_viewers, avg_dwell_seconds among visible=true rows, for shops with unique_viewers >= 2, ordered by unique_viewers DESC then shop_id ASC. Use GROUP BY, HAVING, ORDER BY. B) For 2025-09-01, join shops to visibility_events and compute, by category, the top_of_feed_visibility_rate = visible events with position <= 3 divided by all visible events. Return category and the rate rounded to 3 decimals, sorted DESC. Use a CASE and a JOIN. C) For 2025-09-01, for each user_id, compute seconds_to_next_visible_event using a window function over that user's visible=true events ordered by event_time. Return user_id, event_time, seconds_to_next_visible_event (NULL for the last visible event in the day). D) You’re told to compare new vs. old user engagement using only today’s snapshot (2025-09-01) and to “bucket by duration.” Define user_age_days = DATEDIFF(day, signup_date, DATE '2025-09-01'). Create buckets: [0,7), [7,30), [30,180), [180,INF). Define active_today as having at least one visible=true event on 2025-09-01 with dwell_seconds >= 5. Using only today’s data, compute per bucket: dau_today (distinct users with any visible=true event), active_users_today (distinct users with active_today), and active_rate_today = active_users_today / dau_today. Return bucket_label, dau_today, active_users_today, active_rate_today, sorted by bucket order. Discuss one bias introduced by restricting the denominator to users observed today. E) On 2025-09-01, count distinct users who participated in at least one call (as caller or receiver). Provide two queries: one using UNION and one using UNION ALL, and explain precisely when they produce different counts on the sample data. Then state the general rule for when UNION ALL is safe here and when it will double count. F) Bonus: Using a window function, for each shop_id on 2025-09-01 compute a 3-event moving average of dwell_seconds over time (ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) and return shop_id, event_time, ma3_dwell_seconds.

Shop visibility aggregates with HAVING and ORDER BY

Using the tables below, write a query for the date 2025-09-01 that returns, for each shop_id, the number of unique_viewers and the avg_dwell_seconds among rows with visible = TRUE. Only include shops where unique_viewers >= 2. Order the result by unique_viewers in descending order, then by shop_id in ascending order. Use GROUP BY, HAVING, and ORDER BY. Be careful to ignore invisible rows and to count each user at most once per shop.

Table users

columntypeconstraints
user_idINTPRIMARY KEY
signup_dateDATENOT NULL

Sample data:

signup_dateuser_id
2025-08-311
2025-08-152
2025-06-013
2025-09-014

Table shops

columntypeconstraints
shop_idINTPRIMARY KEY
categoryVARCHAR(50)NOT NULL

Sample data:

categoryshop_id
Bakery10
Grocery11
Pharmacy12

Table visibility_events

columntypeconstraints
event_timeTIMESTAMPNOT NULL
user_idINTNOT NULL
shop_idINTNOT NULL
positionINTNOT NULL
visibleBOOLEANNOT NULL
dwell_secondsINTNOT NULL

Sample data:

dwell_secondsevent_timepositionshop_iduser_idvisible
62025-09-01 09:00:001101true
22025-09-01 09:00:054111true
82025-09-01 09:10:002102true
122025-09-01 10:00:001123true
02025-09-01 10:05:005103false
72025-09-01 11:00:002114true

Table calls

columntypeconstraints
started_atTIMESTAMPNOT NULL
caller_idINTNOT NULL
receiver_idINTNOT NULL

Sample data:

caller_idreceiver_idstarted_at
122025-09-01 08:00:00
312025-09-01 09:30:00
242025-08-31 22:00:00

Expected output

For 2025-09-01, considering only visible = TRUE events, each row shows a shop_id with at least 2 distinct viewers, the count of those distinct viewers, and the average dwell_seconds across their visible events.

avg_dwell_secondsshop_idunique_viewers
7102
4.5112

Hints

  1. Filter to 2025-09-01 and visible = TRUE before aggregating.
  2. Use COUNT(DISTINCT user_id) in HAVING to enforce unique_viewers >= 2.

Top-of-feed visibility rate by shop category

For the date 2025-09-01, join shops to visibility_events and, for each shop category, compute top_of_feed_visibility_rate defined as: (number of visible = TRUE events with position <= 3) divided by (number of all visible = TRUE events). Return category and the rate rounded to 3 decimal places. Sort the result by top_of_feed_visibility_rate in descending order, breaking ties alphabetically by category. Use a JOIN between visibility_events and shops and a CASE expression to identify top-of-feed visibility.

Table users

columntypeconstraints
user_idINTPRIMARY KEY
signup_dateDATENOT NULL

Sample data:

signup_dateuser_id
2025-08-311
2025-08-152
2025-06-013
2025-09-014

Table shops

columntypeconstraints
shop_idINTPRIMARY KEY
categoryVARCHAR(50)NOT NULL

Sample data:

categoryshop_id
Bakery10
Grocery11
Pharmacy12

Table visibility_events

columntypeconstraints
event_timeTIMESTAMPNOT NULL
user_idINTNOT NULL
shop_idINTNOT NULL
positionINTNOT NULL
visibleBOOLEANNOT NULL
dwell_secondsINTNOT NULL

Sample data:

dwell_secondsevent_timepositionshop_iduser_idvisible
62025-09-01 09:00:001101true
22025-09-01 09:00:054111true
82025-09-01 09:10:002102true
122025-09-01 10:00:001123true
02025-09-01 10:05:005103false
72025-09-01 11:00:002114true

Table calls

columntypeconstraints
started_atTIMESTAMPNOT NULL
caller_idINTNOT NULL
receiver_idINTNOT NULL

Sample data:

caller_idreceiver_idstarted_at
122025-09-01 08:00:00
312025-09-01 09:30:00
242025-08-31 22:00:00

Expected output

For each shop category with visible events on 2025-09-01, the result shows the fraction of visible events that appeared in positions 1–3, rounded to 3 decimals, ordered from highest to lowest rate.

categorytop_of_feed_visibility_rate
Bakery1
Pharmacy1
Grocery0.5

Hints

  1. Filter to visible = TRUE events before joining to shops.
  2. Compute the rate as AVG(CASE WHEN position <= 3 THEN 1.0 ELSE 0.0 END).

Seconds to next visible event per user with window functions

For the date 2025-09-01, consider only visibility_events rows where visible = TRUE. For each such event, compute seconds_to_next_visible_event for that user as the number of seconds until their next visible = TRUE event later that day. Use a window function over each user_id, ordered by event_time. Return user_id, event_time, and seconds_to_next_visible_event, with NULL for the last visible event of the day for each user. Order the output by user_id and event_time.

Render event_time as YYYY-MM-DD HH24:MI:SS in the output.

Table users

columntypeconstraints
user_idINTPRIMARY KEY
signup_dateDATENOT NULL

Sample data:

signup_dateuser_id
2025-08-311
2025-08-152
2025-06-013
2025-09-014

Table shops

columntypeconstraints
shop_idINTPRIMARY KEY
categoryVARCHAR(50)NOT NULL

Sample data:

categoryshop_id
Bakery10
Grocery11
Pharmacy12

Table visibility_events

columntypeconstraints
event_timeTIMESTAMPNOT NULL
user_idINTNOT NULL
shop_idINTNOT NULL
positionINTNOT NULL
visibleBOOLEANNOT NULL
dwell_secondsINTNOT NULL

Sample data:

dwell_secondsevent_timepositionshop_iduser_idvisible
62025-09-01 09:00:001101true
22025-09-01 09:00:054111true
82025-09-01 09:10:002102true
122025-09-01 10:00:001123true
02025-09-01 10:05:005103false
72025-09-01 11:00:002114true

Table calls

columntypeconstraints
started_atTIMESTAMPNOT NULL
caller_idINTNOT NULL
receiver_idINTNOT NULL

Sample data:

caller_idreceiver_idstarted_at
122025-09-01 08:00:00
312025-09-01 09:30:00
242025-08-31 22:00:00

Expected output

Each visible event on 2025-09-01 has the number of seconds until the same user’s next visible event that day. Users with only one visible event, or for the last visible event per user, have NULL.

event_timeseconds_to_next_visible_eventuser_id
2025-09-01 09:00:0051
2025-09-01 09:00:05NULL1
2025-09-01 09:10:00NULL2
2025-09-01 10:00:00NULL3
2025-09-01 11:00:00NULL4

Hints

  1. Use LEAD(event_time) OVER (PARTITION BY user_id ORDER BY event_time).
  2. Compute the difference between the next event_time and the current event_time in seconds.
  3. Use PostgreSQL TO_CHAR rather than strftime for timestamp display.

Cohort activity buckets and active rate bias

Cohort Activity Buckets and Active-Rate Selection Bias

Using the users and visibility_events tables, analyze daily activity for 2025-09-01, bucketed by how long each user had been signed up as of that date.

Definitions

  • user_age_days = number of whole days between a user's signup_date and DATE '2025-09-01' (i.e. DATE '2025-09-01' - signup_date). A user who signed up on 2025-09-01 has user_age_days = 0.
  • Assign each user to exactly one signup-age bucket and label it:
    • user_age_days in [0, 7) -> '0-6 days'
    • user_age_days in [7, 30) -> '7-29 days'
    • user_age_days in [30, 180) -> '30-179 days'
    • user_age_days >= 180 -> '180+ days'
  • A user is counted in DAU for the day if they have at least one visibility_events row on 2025-09-01 with visible = TRUE.
  • A user is active_today if they have at least one visibility_events row on 2025-09-01 with visible = TRUE and dwell_seconds >= 5.

Task

For each of the four buckets (every bucket must appear in the output, even if it has zero users that day), return:

  • bucket_label — the bucket name.
  • dau_today — count of distinct users in that bucket who have any visible = TRUE event on 2025-09-01.
  • active_users_today — count of distinct users in that bucket who are active_today.
  • active_rate_today — active_users_today / dau_today, rounded to 4 decimal places, or NULL when dau_today = 0.

Sort the rows in bucket order from youngest to oldest ('0-6 days', '7-29 days', '30-179 days', '180+ days').

Also briefly note one bias introduced by restricting the denominator (dau_today) to users who were observed today — i.e. only users with at least one visibility event on 2025-09-01.

Table users

columntypeconstraints
user_idINTPRIMARY KEY
signup_dateDATENOT NULL

Sample data:

signup_dateuser_id
2025-08-311
2025-08-152
2025-06-013
2025-09-014
2025-01-015

Table visibility_events

columntypeconstraints
event_timeTIMESTAMPNOT NULL
user_idINTNOT NULL
shop_idINTNOT NULL
positionINTNOT NULL
visibleBOOLEANNOT NULL
dwell_secondsINTNOT NULL

Sample data:

dwell_secondsevent_timepositionshop_iduser_idvisible
62025-09-01 09:00:001101true
22025-09-01 09:00:054111true
82025-09-01 09:10:002102true
122025-09-01 10:00:001123true
02025-09-01 10:05:005103false
72025-09-01 11:00:002114true
32025-09-01 12:00:003125true

Expected output

One row per signup-age bucket on 2025-09-01: dau_today (distinct users with any visible=TRUE event), active_users_today (distinct users with a visible event of dwell_seconds>=5), and active_rate_today (active/dau, NULL when dau=0), ordered youngest to oldest bucket.

active_rate_todayactive_users_todaybucket_labeldau_today
1.000020-6 days2
1.000017-29 days1
1.0000130-179 days1
0.00000180+ days1

Hints

  1. PostgreSQL has no DATEDIFF — subtract one DATE from another (DATE '2025-09-01' - signup_date) to get an integer number of days.
  2. Compute one row per user from visibility_events first (DAU membership plus a MAX flag for dwell_seconds >= 5), then LEFT JOIN a fixed 4-row bucket list so empty buckets still appear.
  3. Guard the ratio with NULLIF(dau, 0) and cast to numeric so the divide-by-zero bucket returns NULL, not an error.

Distinct call participants: UNION vs UNION ALL

On 2025-09-01, count distinct users who participated in at least one call, either as caller or as receiver. Show the effect of using UNION versus UNION ALL: write logic that uses UNION to deduplicate caller and receiver IDs, and logic that uses UNION ALL without deduplication, and report both counts side by side. Then explain, based on the sample data, when these two approaches produce different counts and state the general rule for when UNION ALL is safe here versus when it will double count participants.

Table users

columntypeconstraints
user_idINTPRIMARY KEY
signup_dateDATENOT NULL

Sample data:

signup_dateuser_id
2025-08-311
2025-08-152
2025-06-013
2025-09-014

Table shops

columntypeconstraints
shop_idINTPRIMARY KEY
categoryVARCHAR(50)NOT NULL

Sample data:

categoryshop_id
Bakery10
Grocery11
Pharmacy12

Table visibility_events

columntypeconstraints
event_timeTIMESTAMPNOT NULL
user_idINTNOT NULL
shop_idINTNOT NULL
positionINTNOT NULL
visibleBOOLEANNOT NULL
dwell_secondsINTNOT NULL

Sample data:

dwell_secondsevent_timepositionshop_iduser_idvisible
62025-09-01 09:00:001101true
22025-09-01 09:00:054111true
82025-09-01 09:10:002102true
122025-09-01 10:00:001123true
02025-09-01 10:05:005103false
72025-09-01 11:00:002114true

Table calls

columntypeconstraints
started_atTIMESTAMPNOT NULL
caller_idINTNOT NULL
receiver_idINTNOT NULL

Sample data:

caller_idreceiver_idstarted_at
122025-09-01 08:00:00
312025-09-01 09:30:00
242025-08-31 22:00:00

Expected output

The single-row result shows two counts for 2025-09-01: distinct_participants_union counts users in the UNION of caller_id and receiver_id (no duplicates across roles); rows_union_all counts total rows produced by UNION ALL (callers and receivers concatenated without deduplication). On this data, UNION yields 3 participants (users 1, 2, 3), while UNION ALL yields 4 rows because user 1 appears twice (once as caller, once as receiver). In general, UNION ALL is safe if you know the two sets of IDs cannot overlap, or if you later apply DISTINCT on user_id; otherwise it can double count users who appear in both roles or multiple times.

distinct_participants_unionrows_union_all
34

Hints

  1. Build one derived table with UNION and another with UNION ALL, each over caller_id/receiver_id filtered to 2025-09-01.
  2. Think about what happens when the same user appears as both caller and receiver on the same day.

3-event moving average of dwell time per shop

For each shop_id on 2025-09-01, compute a 3-event moving average of dwell_seconds over time using a window function. The window for each row should be defined as ROWS BETWEEN 2 PRECEDING AND CURRENT ROW, partitioned by shop_id and ordered by event_time. Include both visible and invisible events (i.e., do not filter on visible). Return shop_id, event_time, and ma3_dwell_seconds (the 3-event moving average), ordered by shop_id and event_time.

Render event_time as YYYY-MM-DD HH24:MI:SS in the output.

Table users

columntypeconstraints
user_idINTPRIMARY KEY
signup_dateDATENOT NULL

Sample data:

signup_dateuser_id
2025-08-311
2025-08-152
2025-06-013
2025-09-014

Table shops

columntypeconstraints
shop_idINTPRIMARY KEY
categoryVARCHAR(50)NOT NULL

Sample data:

categoryshop_id
Bakery10
Grocery11
Pharmacy12

Table visibility_events

columntypeconstraints
event_timeTIMESTAMPNOT NULL
user_idINTNOT NULL
shop_idINTNOT NULL
positionINTNOT NULL
visibleBOOLEANNOT NULL
dwell_secondsINTNOT NULL

Sample data:

dwell_secondsevent_timepositionshop_iduser_idvisible
62025-09-01 09:00:001101true
22025-09-01 09:00:054111true
82025-09-01 09:10:002102true
122025-09-01 10:00:001123true
02025-09-01 10:05:005103false
72025-09-01 11:00:002114true

Table calls

columntypeconstraints
started_atTIMESTAMPNOT NULL
caller_idINTNOT NULL
receiver_idINTNOT NULL

Sample data:

caller_idreceiver_idstarted_at
122025-09-01 08:00:00
312025-09-01 09:30:00
242025-08-31 22:00:00

Expected output

For each visibility event on 2025-09-01, grouped by shop_id and ordered by event_time, ma3_dwell_seconds is the average dwell_seconds over the current row and up to the two immediately preceding rows for that shop (fewer rows at the start). Invisible events (visible = FALSE) are included and contribute their dwell_seconds (e.g., 0) to the moving average.

event_timema3_dwell_secondsshop_id
2025-09-01 09:00:00610
2025-09-01 09:10:00710
2025-09-01 10:05:004.66710
2025-09-01 09:00:05211
2025-09-01 11:00:004.511
2025-09-01 10:00:001212

Hints

  1. Use AVG(dwell_seconds) as a window function with ROWS BETWEEN 2 PRECEDING AND CURRENT ROW.
  2. Partition by shop_id and order by event_time; do not filter on visible.
  3. Use PostgreSQL TO_CHAR rather than strftime for timestamp display.