# Most Common Monday Outcome > Mondays have a pattern. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L3 ## Problem The experimentation platform tracks when experiments are created. Among experiments created on a Monday, what are the outcome values and how often does each occur? Rank sorted from most common to least. ## Worked solution and explanation ### Why this problem exists in real interviews Day-of-week filters look trivial and are where people lose the question. They want to see if you state the weekday convention (`strftime('%w')` makes Sunday=0, so Monday=1) before writing, and whether you flag that ranking outcomes without a tiebreaker is a silent bug. The experimentation platform context matters: outcomes are a small enum, so ties are likely. --- ### Break down the requirements #### Step 1: State the weekday convention Say out loud: 'SQLite's `strftime('%w', created)` returns 0 for Sunday through 6 for Saturday, so Monday is 1.' If the interviewer uses Postgres, `EXTRACT(DOW)` also makes Sunday=0; in MySQL `DAYOFWEEK` makes Sunday=1. #### Step 2: Cast before comparing `strftime` returns text. `'1' = 1` is false in strict comparisons. Wrap with `CAST(... AS INTEGER) = 1` or compare to the string `'1'`. Pick one and be consistent. #### Step 3: Group and count `GROUP BY outcome`, `COUNT(*) AS cnt`. The prompt asks for every outcome ranked, not just the top one, so no `LIMIT 1`. #### Step 4: Order by frequency `ORDER BY cnt DESC`. Mention the tie case: if you needed a single winner, `LIMIT 1` picks a non-deterministic row among ties. Add a secondary sort like `outcome ASC` only if asked. --- ### The solution **MONDAY OUTCOME FREQUENCY** ```sql SELECT outcome, COUNT(*) AS cnt FROM experiments WHERE CAST(strftime('%w', created) AS INTEGER) = 1 GROUP BY outcome ORDER BY cnt DESC ``` > **Cost Analysis** > > 1.5M rows with a function-on-column predicate kills any index on `created`. Full scan, hash aggregate on `outcome`. In production, materialize `day_of_week` as a generated column or partition by date; for an interview, name the cost and move on. > **Interviewers Watch For** > > Before writing, ask: 'Which weekday convention does this dialect use?' Then: 'If two outcomes tie for most common, do you want both or a deterministic pick?' Skipping the convention check is the failure mode here; the SQL itself is easy. > **Common Pitfall** > > Writing `strftime('%w', created) = 1` without the `CAST`. SQLite is loose enough to coerce in some contexts, but in stricter engines this returns no rows and you spend ten minutes debugging an empty result. Cast explicitly or quote the literal. --- ### COMMON FOLLOW-UP QUESTIONS ## Common follow-up questions - How would you return just the single most common Monday outcome? _(Probes whether you reach for `LIMIT 1` and call out the silent tie-break problem.)_ - How would you do this per `platform`, picking the top outcome for each? _(Probes window-function dedup: `ROW_NUMBER() OVER (PARTITION BY platform ORDER BY cnt DESC)`.)_ - What if `created` is stored as UTC but Monday should be in the user's local timezone? _(Probes timezone awareness and whether you'd join `user_id` to a user timezone table before extracting day-of-week.)_ - How would you compare Monday outcome distribution to the rest of the week? _(Probes conditional aggregation: `SUM(CASE WHEN dow = 1 THEN 1 END)` vs `SUM(CASE WHEN dow <> 1 THEN 1 END)` grouped by `outcome`.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/most_common_monday_outcome) - [SQL Interview Questions](https://datadriven.io/sql-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.