Most Popular Signup Day

A medium SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain
SQL
Difficulty
medium
Seniority
L4
Asked in
Airbnb

Problem

For each weekday, give us how many users signed up on it and the fraction of those signups that reported an age bracket, as a decimal between 0 and 1. Our promotions only run Monday through Friday, so weekend signups stay out of it.

One day of the week wins on signups.

Tables

users

Columns: user_id INTEGER, username TEXT, email TEXT, signup_date TEXT, account_status TEXT, age_bucket TEXT

users: 10 of 200 rows
user_idusernameemailsignup_dateaccount_statusage_bucket
100alicealice@example.com2025-02-02inactive25-34
197aaron42aaron42@example.com2026-03-03suspended35-44
294 ameliaamelia@example.com2024-04-04pending_verification45-54
391arjun arjun@example.com2025-05-05active55-64
488ava99ava99@example.com2026-06-06inactive65+
585andrew_kandrew_k@example.com2024-07-07suspendedNULL
682anikaanika@example.com2025-08-08 16:34:00pending_verification18-24
779aidenaiden@example.com2026-09-09 14:53:00active25-34
876aria_baria_b@example.com2024-10-10inactive35-44
973brianbrian@example.com2025-11-11suspended45-54

Expected output

First 5 of 5 rows
signup_daysignup_countage_reported_rate
Monday310.8709677419354839
Tuesday280.9642857142857143
Wednesday260.8846153846153846
Thursday300.9
Friday280.8214285714285714

Worked solution and explanation

What this is really asking

Strip the costume and this is a data-completeness rate per bucket over a business-day-only population: label each signup with its weekday, drop the weekend, then divide the signups that filled in age_bucket by all signups in the bucket. Everyone writes the CASE and the GROUP BY. What separates candidates is the division. Both counts are integers, and integer divided by integer truncates, so a day where 26 of 30 signups reported a bracket comes back as 0. Get that wrong and every weekday reads 0, which says nobody ever tells you their age and sends someone chasing a broken signup form that is working fine.

Building it step by step

Step 1: Turn the date into a weekday name

STRFTIME('%w', signup_date) returns the weekday as a text digit, zero-indexed at Sunday. A simple CASE on that digit maps '1' to 'Monday' through '5' to 'Friday'; compare against text literals, because text is what the function gives you.

Step 2: Keep only the business days

Sunday is '0' and Saturday is '6'. The WHERE collapses those two to NULL with a CASE and keeps rows that are IS NOT NULL, so only business days reach the aggregate. Skip it and you get two extra buckets with a NULL day name.

Step 3: Let the column count do the filtering

A skipped age question is stored as NULL, and COUNT(age_bucket) skips NULL on its own, so it counts exactly the signups that reported a bracket. COUNT(*) counts every row and is the day's total. No condition needed: the column count already is the filter.

Step 4: Divide as real numbers

Group by the weekday digit, not the label. Wrap COUNT(age_bucket) in CAST(... AS REAL) before dividing by COUNT(*), and the ratio survives as a fraction between 0 and 1.

Business-day age-reporting share per weekday
SELECT
    CASE STRFTIME('%w', signup_date)
        WHEN '1' THEN 'Monday'
        WHEN '2' THEN 'Tuesday'
        WHEN '3' THEN 'Wednesday'
        WHEN '4' THEN 'Thursday'
        WHEN '5' THEN 'Friday'
    END AS signup_day,
    COUNT(*) AS signup_count,
    CAST(COUNT(age_bucket) AS REAL) / COUNT(*) AS age_reported_rate
FROM users
WHERE (CASE WHEN STRFTIME('%w', signup_date) IN ('0', '6') THEN NULL ELSE 1 END) IS NOT NULL
GROUP BY STRFTIME('%w', signup_date)

The WHERE nulls the weekend and keeps business days; the CAST on COUNT(age_bucket) turns integer division into a real fraction.

Integer division
COUNT(age_bucket) / COUNT(*) gives 26 / 30 = 0. Every weekday reads 0, and nothing errors.
Real division
CAST(COUNT(age_bucket) AS REAL) / COUNT(*) gives 26.0 / 30 = 0.867. One cast on either operand is enough.

Common follow-up questions