Monthly Fantasy Contest Entries and the Change From Last Month
The fantasy app's growth team tracks contest entries month by month.
- Difficulty: Medium
- Topics: Window Functions, Dates
- Dialect: MySQL
- Problem: #463
Problem statement
The fantasy app's growth team tracks contest entries month by month. For each calendar month that has at least one entry, report the entries, the fees collected and the change from the previous calendar month.
Return the columns month (as YYYY-MM), entries, fees_collected (sum of entry_fee) and change_from_prev = this month's entries minus the previous calendar month's entries. A previous month with no entries counts as 0; the earliest month in the data has change_from_prev NULL. Order the rows by month.
Tables
Table: ContestEntry
| Column | Type |
|---|---|
| entry_id | int |
| user_id | int |
| entered_at | datetime |
| entry_fee | int |
Primary key: entry_id.
One row per paid contest entry; entry_fee is in rupees.
Examples
Example 1
ContestEntry
| entry_id | user_id | entered_at | entry_fee |
|---|---|---|---|
| 1 | 11 | 2024-03-02 19:05:00 | 49 |
| 2 | 12 | 2024-03-15 20:10:00 | 99 |
| 3 | 11 | 2024-04-01 18:45:00 | 49 |
| 4 | 13 | 2024-04-09 21:00:00 | 25 |
| 5 | 14 | 2024-04-30 23:59:00 | 49 |
| 6 | 12 | 2024-06-03 19:30:00 | 99 |
| 7 | 15 | 2024-07-11 20:00:00 | 25 |
| 8 | 11 | 2024-07-21 19:15:00 | 49 |
Output
| month | entries | fees_collected | change_from_prev |
|---|---|---|---|
| 2024-03 | 2 | 148 | NULL |
| 2024-04 | 3 | 123 | 1 |
| 2024-06 | 1 | 99 | 1 |
| 2024-07 | 2 | 74 | 1 |
How to solve Monthly Fantasy Contest Entries and the Change From Last Month
Step one is a monthly rollup: group by DATE_FORMAT(entered_at, '%Y-%m'), counting entries and summing fees. The YYYY-MM text sorts chronologically, which the ordering relies on.
The subtle part is the calendar gap. LAG(entries) OVER (ORDER BY month) returns the previous row, and in the example the row before June is April — May had no entries. Comparing June with April would be wrong; the statement says May counts as 0. So either join each month to the row for exactly one calendar month earlier — DATE_FORMAT(DATE_SUB(CONCAT(month, '-01'), INTERVAL 1 MONTH), '%Y-%m') — with a LEFT JOIN and COALESCE(..., 0), or use LAG for both the previous month and its entries and check that the previous row really is the previous month, falling back to entries - 0.
The earliest month has no history at all and gets NULL rather than its own count — that is a separate branch in both versions. The work is one grouping plus a join or a sort over a handful of monthly rows.
Reference solution (MySQL)
WITH monthly AS (
SELECT DATE_FORMAT(entered_at, '%Y-%m') AS month, COUNT(*) AS entries, SUM(entry_fee) AS fees_collected
FROM ContestEntry
GROUP BY DATE_FORMAT(entered_at, '%Y-%m')
)
SELECT m.month, m.entries, m.fees_collected,
CASE
WHEN m.month = (SELECT MIN(month) FROM monthly) THEN NULL
ELSE m.entries - COALESCE(p.entries, 0)
END AS change_from_prev
FROM monthly m
LEFT JOIN monthly p
ON p.month = DATE_FORMAT(DATE_SUB(CONCAT(m.month, '-01'), INTERVAL 1 MONTH), '%Y-%m')
ORDER BY m.monthAnother way
WITH monthly AS (
SELECT DATE_FORMAT(entered_at, '%Y-%m') AS month, COUNT(*) AS entries, SUM(entry_fee) AS fees_collected
FROM ContestEntry GROUP BY DATE_FORMAT(entered_at, '%Y-%m')
), lagged AS (
SELECT month, entries, fees_collected,
LAG(month) OVER (ORDER BY month) AS prev_month,
LAG(entries) OVER (ORDER BY month) AS prev_entries
FROM monthly
)
SELECT month, entries, fees_collected,
CASE
WHEN prev_month IS NULL THEN NULL
WHEN prev_month = DATE_FORMAT(DATE_SUB(CONCAT(month, '-01'), INTERVAL 1 MONTH), '%Y-%m') THEN entries - prev_entries
ELSE entries
END AS change_from_prev
FROM lagged
ORDER BY month← Share of Seats Booked Two Weeks Early for Each Match · Wickets Credited to Each Bowler From the Dismissal Text →