Date Series Gap-Fill with Event Counts
Preview mode. Log in to edit, run, submit, and save progress.
Description
You are given an Events table and a DateRange table with a single row containing a start and end date. Write a SQL query that generates every calendar date in [start_date, end_date] using a recursive CTE, and for each date reports the total event count, click count, and view count - even for dates with no events (showing 0s). Return (event_date, total_events, clicks, views) ordered by event_date. Table: Events
| Column Name | Type | Description |
|---|---|---|
| event_id | INT | Primary key |
| event_date | DATE | Date of the event |
| event_type | VARCHAR | Either 'click' or 'view' |
Table: DateRange
| Column Name | Type | Description |
|---|---|---|
| start_date | DATE | Range start |
| end_date | DATE | Range end |
Database Schema (Inferred)
Events
| Column Name | Example Value |
|---|---|
| event_id | 1 |
| event_date | 2024-01-01 |
| event_type | click |
DateRange
| Column Name | Example Value |
|---|---|
| start_date | 2024-01-01 |
| end_date | 2024-01-07 |
Example
Events
| event_id | event_date | event_type |
|---|---|---|
| 1 | 2024-01-01 | click |
| 2 | 2024-01-01 | click |
| 3 | 2024-01-03 | view |
| 4 | 2024-01-05 | click |
| 5 | 2024-01-05 | view |
| 6 | 2024-01-05 | click |
DateRange
| start_date | end_date |
|---|---|
| 2024-01-01 | 2024-01-07 |
Output
| event_date | total_events | clicks | views |
|---|---|---|---|
| 2024-01-01 | 2 | 2 | 0 |
| 2024-01-02 | 0 | 0 | 0 |
| 2024-01-03 | 1 | 0 | 1 |
| 2024-01-04 | 0 | 0 | 0 |
| 2024-01-05 | 3 | 2 | 1 |
| 2024-01-06 | 0 | 0 | 0 |
| 2024-01-07 | 0 | 0 | 0 |
Explanation:
Generate the full date range with a recursive CTE. LEFT JOIN events to preserve gap dates. Use conditional aggregation to split clicks vs views.
Approach hint
Start with a simple approach, explain the trade-off, then move toward a cleaner or more scalable solution.
Common mistake
Skipping assumptions, edge cases, or trade-offs can make an otherwise good answer feel incomplete.
Events
| event_id | event_date | event_type |
|---|---|---|
| 1 | 2024-01-01 | click |
| 2 | 2024-01-01 | click |
| 3 | 2024-01-03 | view |
| 4 | 2024-01-05 | click |
| 5 | 2024-01-05 | view |
| 6 | 2024-01-05 | click |
DateRange
| start_date | end_date |
|---|---|
| 2024-01-01 | 2024-01-07 |
Output
| event_date | total_events | clicks | views |
|---|---|---|---|
| 2024-01-01 | 2 | 2 | 0 |
| 2024-01-02 | 0 | 0 | 0 |
| 2024-01-03 | 1 | 0 | 1 |
| 2024-01-04 | 0 | 0 | 0 |
| 2024-01-05 | 3 | 2 | 1 |
| 2024-01-06 | 0 | 0 | 0 |
| 2024-01-07 | 0 | 0 | 0 |