# Events by Month Across Years

> Month by month, year by year. The pattern emerges.

Canonical URL: <https://datadriven.io/problems/events_by_month_across_years>

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

The product analytics team suspects traffic is seasonal and wants to see the pattern across all available years. Show total event counts for each calendar month (1 through 12), combining all years into a single row per month.

## Worked solution and explanation

### Why this problem exists in real interviews

This tests window functions for per-group retrieval. The interviewer checks whether you can use `ROW_NUMBER` with `PARTITION BY` to extract one row per `event_type`.

---

### Break down the requirements

#### Step 1: Partition by `event_type`

`PARTITION BY event_type` creates groups. Within each group, `ORDER BY event_timestamp DESC` determines the ranking.

#### Step 2: Filter to rank 1

`WHERE rnk = 1` in the outer query selects the target row per group.

---

### The solution

**ROW_NUMBER for per-group top row**

```sql
SELECT *
FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY event_type ORDER BY event_timestamp DESC) AS rnk
    FROM event_data
) ranked
WHERE rnk = 1
ORDER BY event_type
```

> **Cost Analysis**
>
> Window function sorts within each `event_type` partition. An index on `(event_type, event_timestamp)` avoids a full sort.

> **Interviewers Watch For**
>
> The interviewer checks whether you use ROW_NUMBER (one row) vs. RANK/DENSE_RANK (ties) based on the prompt requirements.

> **Common Pitfall**
>
> Using GROUP BY with MIN(event_timestamp) gives the value but not the other columns. ROW_NUMBER gives the full row.

---

## Common follow-up questions

- What if there are ties? _(Tests RANK or DENSE_RANK for tie-inclusive selection.)_
- How would you get both the first and last per group? _(Tests dual window functions or UNION.)_
- Could you solve this without window functions? _(Tests correlated subquery or self-join alternatives.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/events_by_month_across_years)
- [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.