Most Common Monday Outcome

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

L3

Asked in

Meta

Problem

Experiments we create on a Monday seem to end with no recorded outcome more often than with any real result. Across the Monday-created rows of experiments, show how often each outcome value occurs, most common first.

Mondays have a pattern.

Tables

experiments

Columns: exp_id INTEGER, exp_name TEXT, variant TEXT, user_id INTEGER, outcome REAL, created TEXT, platform TEXT

Expected output

Worked solution and explanation

What this really is

This is a frequency count over a nullable column, dressed up as an experiment report, with a calendar filter in front. Two things are probed. First, can you pick Mondays out of created at all: they are scattered across five years of dates, so no range or prefix does it, only the weekday of each date. Second, and this is where people lose it, do you keep the experiments with no recorded outcome as a group of their own. Write WHERE outcome IS NOT NULL or COUNT(outcome) and the most common Monday result on the seed, the missing-outcome group with 5 experiments, disappears or drops to 0 at the bottom.

Step 1: Pin the weekday convention first

strftime('%w', created) numbers the days 0 for Sunday through 6 for Saturday, so Monday is 1. Say it out loud before writing: Postgres EXTRACT(DOW FROM ...) agrees, while MySQL DAYOFWEEK starts Sunday at 1 and would make Monday 2.

Step 2: Cast the weekday before comparing

strftime returns text, and the text '1' never equals the integer 1, so an uncast comparison returns no rows at all and raises no error. CAST(... AS INTEGER) = 1 makes it a numeric comparison.

Step 3: Count rows, not outcomes

GROUP BY outcome collects every missing outcome into one NULL group. COUNT(*) counts the rows in it; COUNT(outcome) counts only non-null values and reports 0 for exactly the group that matters most.

Step 4: Sort by the count and stop

ORDER BY cnt DESC is the whole order the question asks for. Most outcomes appear once, and those ties may come back in any order: nobody asked for a winner among them, so there is no tiebreaker and no LIMIT.

SELECT outcome, COUNT(*) AS cnt
FROM experiments
WHERE CAST(strftime('%w', created) AS INTEGER) = 1
GROUP BY outcome
ORDER BY cnt DESC

Drops the missing outcomes

COUNT(outcome) returns the NULL row with cnt 0, and WHERE outcome IS NOT NULL removes it entirely. Either way the answer opens with a value that appears twice.

Keeps them as a group

COUNT(*) returns the NULL row with cnt 5, at the top: on Mondays, no recorded outcome is more common than any real result.

Follow-up questions

Common follow-up questions

• How would you return just the single most common Monday outcome? (Probes whether you notice that `LIMIT 1` needs a tie rule once two outcomes share the top count.)
• How would you do this per `platform`, picking the top outcome for each? (Probes per-group selection, for example `ROW_NUMBER() OVER (PARTITION BY platform ORDER BY cnt DESC)`, and whether you normalize the mixed-case `platform` values first.)
• What if `created` is stored as UTC but Monday should be in the user's local timezone? (Probes timezone awareness: the weekday must be taken after converting to local time.)
• How would you compare the Monday outcome distribution to the rest of the week? (Probes conditional aggregation: one count for Monday rows and one for the others, side by side per `outcome`.)