A useful SQL challenge gives you a schema, example rows and an exact result contract. This guide uses activity events to practise deduplication, grouped counts and deterministic ranking. The central problem keeps one event per user and day while preserving the chosen source record’s identity.
Write your first query before reading the worked answer. Then explain why each output row appears and why each excluded row disappears. A query is not complete merely because it runs: it must handle the specified ties, missing values and repeated events without quietly changing the question.
Define the unit being deduplicated
The fixture records events, not necessarily unique active days. User 1 has two events on January 1, so counting event rows differs from counting active user-days. The first challenge asks for one row per user-and-day combination, selecting the smallest event id when several events share that combination. That tie policy is explicit and observable.
SELECT DISTINCT user_id,event_day produces a unique pair list but loses event id and event type. DISTINCT over every event column would preserve both different events. To retain one full source row per pair, rank within the pair and filter the selected position. Do not invent a business duplicate definition from equal values in just one convenient column.
Challenge one: choose one row per group
ROW_NUMBER() OVER (PARTITION BY user_id,event_day ORDER BY id) assigns a distinct position inside each user-day group. The smallest id gets one. A CTE exposes that position to an outer WHERE rn=1, because the same-level WHERE cannot normally filter a window calculation directly. The final ORDER BY describes presentation independently of the window’s selection rule.
If id is unique, the rule is deterministic. If the source used a nonunique event timestamp instead, add an appropriate unique key to resolve ties. Choosing earliest timestamp and choosing smallest id are not inherently the same policy; state which one represents the challenge requirement. A query that chooses an arbitrary peer can pass today’s sample while failing a later tie test.
Challenge two: count observations at the right grain
COUNT(*) on events counts every event. COUNT(DISTINCT event_day) grouped by user_id counts known active days per user. The latter does not require physically deleting or updating repeated events. Both measures can be useful, but their labels must identify whether they describe occurrences or distinct days. A dataset containing only one event per day cannot expose the difference.
If a day field were nullable, COUNT(DISTINCT event_day) would omit unknown days. A cleaning report should count those separately rather than quietly treating an unknown event date as a genuine shared day. This fixture requires event_day, so its distinct-day counts are complete for the supplied events. The requirement must be revisited if the schema changes.
Challenge three: identify consecutive runs
A consecutive-day streak is more demanding than a day count. First deduplicate user-days, then order dates per user and compare adjacent days through a suitable LAG or date arithmetic. A gaps-and-islands method can group a date offset by its ordered position, but the date subtraction syntax is dialect-specific. Calendar-day adjacency is not a fixed row-count assumption.
User 1 has January 1, January 2 and January 4, so its longest sample streak is two, not three. User 2 has January 1 and January 2, also a two-day streak. The repeated January 1 event must not inflate a streak length. Test a gap, a duplicate and a one-day user before claiming a streak solution is correct.
Challenge four: top groups with ties
Ranking users by their active-day count requires grouping before ranking. A ROW_NUMBER result chooses individual positions, while DENSE_RANK identifies distinct count levels and can retain tied users. Decide whether a top-two task means exactly two users or all users in the first two distinct ranks. The two contracts can produce different row counts.
Keep the ordering measure and identity separate. A unique user-id tie-breaker can make an exact-size leaderboard deterministic, but adding it to a peer ranking changes which rows count as tied. Explain that choice before writing OVER. A reliable solution includes a tie in the test data rather than assuming the input naturally orders every user uniquely.
Validate and extend the challenge
Use expected rows as evidence, then add one targeted test. Another event on an existing user-day should not increase the distinct-day count or create an extra deduplicated row. A genuinely new day should increase that user’s count. Changing the chosen first event’s type should change the retained type without changing the number of pairs.
For performance practice, add realistic volumes only after correctness is established. An index on user_id,event_day,id may help the partition and ordering pattern, but plan choice is engine-dependent. Keep the original six-row fixture as a regression test so an optimisation cannot silently alter deduplication, tie policy or final ordering while appearing faster.
Run the complete practice setup
These six events contain one repeated user-day, a consecutive pair and a later gap. Each id is a real event identity. The schema and main window query work in modern PostgreSQL, MySQL 8+, SQL Server and SQLite with window support.
CREATE TABLE challenge_events (
id INTEGER PRIMARY KEY,user_id INTEGER NOT NULL,
event_day DATE NOT NULL,event_type VARCHAR(20) NOT NULL
);
INSERT INTO challenge_events (id,user_id,event_day,event_type) VALUES
(1,1,'2026-01-01','view'),(2,1,'2026-01-01','click'),
(3,1,'2026-01-02','view'),(4,1,'2026-01-04','view'),
(5,2,'2026-01-01','view'),(6,2,'2026-01-02','click');Practical exercise and worked solution
Return each user’s event count and distinct active-day count side by side. Explain why user 1’s measures differ.
SELECT user_id,COUNT(*) AS event_count,
COUNT(DISTINCT event_day) AS active_days
FROM challenge_events GROUP BY user_id ORDER BY user_id;user_id | event_count | active_days
1 | 4 | 3
2 | 2 | 2User 1 has four event rows across three dates because January 1 appears twice. User 2 has two events on different dates, so both measures equal two. No source records have been deleted. The query supplies two different metrics at one-user grain and does not equate repeated daily activity with invalid stored events.
Official documentation
See the PostgreSQL window tutorial, SQLite window specification and PostgreSQL WITH guide for the functions and scopes used in the solution.
SQL Coding Challenges with Test Data and Worked Solutions Examples
Each example shows the code, the output it produces and what each part does.
Retain the earliest id for each user-day
Events 1 and 2 share the user-day key, so the id ordering selects event 1 and excludes event 2. Every other pair has one event and remains. The answer contains five real chosen records, not a projected list that has discarded their identity and type.
WITH numbered AS (
SELECT id,user_id,event_day,event_type,
ROW_NUMBER() OVER(PARTITION BY user_id,event_day ORDER BY id) AS rn
FROM challenge_events
)
SELECT id,user_id,event_day,event_type FROM numbered
WHERE rn=1 ORDER BY user_id,event_day,id;id | user_id | event_day | event_type
1 | 1 | 2026-01-01 | view
3 | 1 | 2026-01-02 | view
4 | 1 | 2026-01-04 | view
5 | 2 | 2026-01-01 | view
6 | 2 | 2026-01-02 | clickCommon SQL Coding Challenges with Test Data and Worked Solutions Mistakes
Using DISTINCT without defining the selected grain
SELECT DISTINCT user_id,event_day,event_type keeps both events on user 1’s first day because view and click differ. Removing event_type produces a unique day list but still does not choose a source event id. The challenge asks for a selected full row, so a ranking or another explicit representative-row rule is required. Do not widen or shrink the DISTINCT projection until the intended duplicate definition is clear.
SQL Coding Challenges with Test Data and Worked Solutions FAQ
Must a challenge use an advanced function?
No. The simplest clear query meeting the contract is a strong answer. A plain GROUP BY that is correct beats a clever window function that mishandles ties, and you can mention the alternative if it would scale better.
Can DISTINCT replace row ranking?
Only when the required output is the distinct projected values rather than a chosen source record. "Each customer's latest order" needs a whole row chosen by a rule, which ROW_NUMBER() or a correlated filter provides and DISTINCT can't.
Should I benchmark on the sample?
No. Use the sample for correctness: it's far too small to show performance differences. Representative larger data, with realistic distributions and indexes, is needed for meaningful performance investigation.
Where can I practise SQL challenges online?
Sites such as LeetCode, HackerRank, StrataScratch and SQLZoo offer graded SQL problems in several dialects. Mix them with the exercises by difficulty and business problems in this course, which use the same small datasets as the lessons.
How do I check my answer when no expected output is given?
Work out the expected rows by hand from the sample data first, including edge cases such as ties and NULLs. Then compare row counts, check one row from each tricky category, and try a deliberately different correct approach to see whether both agree.
SQL Coding Challenges with Test Data and Worked Solutions Exercises
Answer each one and check it instantly. If you get stuck, open the hint before the answer.
Which event is excluded by the user-day selection rule?
SQL Coding Challenges with Test Data and Worked Solutions Quiz
2 questions to check what you learned.
What does PARTITION BY user_id,event_day establish?
- Separate ranking groups for each complete user-day pair
- One global rank for all users
- A permanent unique constraint on the table
What should a second event on an existing day change?
- Every existing day must be deleted
- Event count can increase while distinct active-day count stays unchanged
- Distinct-day count must always increase