Monthly Signup Counts

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

Domain
SQL
Difficulty
easy
Seniority
L3
Asked in
General Assembly

Problem

Produce each month's signup count from the users table in two forms, the whole number the dashboard shows and a floating-point copy, listed from the oldest month forward. An analyst feeds that copy into a forecasting model that only accepts floating-point inputs, so an integer in that column will not load.

Signups, month by month.

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 24 rows
monthsignup_countsignup_count_float
2023-0188
2023-0599
2023-0988
2024-0188
2024-041717

Worked solution and explanation

This is a monthly bucket count with one twist, and the whole grade rides on it: the same number has to leave the query twice, in two different types. Anyone can turn signup_date into a month and count rows. What separates candidates is noticing that COUNT(*) is an integer however you alias it, so selecting it a second time ships 8 where the model was promised 8.0. Skip the explicit conversion and the result looks right in a terminal, then fails the moment a type-strict consumer reads it.

Building it

Step 1: Bucket each signup by its month

Format signup_date as a year-month label with STRFTIME('%Y-%m', signup_date). Two rows in users carry a time of day ('2025-08-08 16:34:00'). The formatting throws the time away, so those signups fall into their month like every other row.

Step 2: Count once, emit twice

Group on that month expression and take COUNT(*). The dashboard column is the count as is. The model column is the same aggregate passed through CAST(COUNT(*) AS DOUBLE). The engine computes the aggregate once per group; the cast only changes how the value is typed.

Step 3: Walk the calendar forward

Sort by month. Because the label is zero-padded, text order and calendar order agree, so '2023-09' sorts before '2023-10' without parsing anything back into a date.

Dual-type output for downstream compatibility
SELECT STRFTIME('%Y-%m', signup_date) AS month,
    COUNT(*) AS signup_count,
    CAST(COUNT(*) AS DOUBLE) AS signup_count_float
FROM users
GROUP BY STRFTIME('%Y-%m', signup_date)
ORDER BY month
Count selected twice
COUNT(*) AS signup_count_float returns 8, 9, 17. The column name promises a float and the value is still an integer, so the model rejects it.
Count cast to a double
CAST(COUNT(*) AS DOUBLE) returns 8.0, 9.0, 17.0. Same value, declared type, and the integer column next to it is untouched.

Common follow-up questions