Monthly Fantasy Contest Entries and the Change From Last Month

Medium
463.

Monthly Fantasy Contest Entries and the Change From Last Month

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

ColumnType
entry_idint
user_idint
entered_atdatetime
entry_feeint

Primary key: entry_id.

One row per paid contest entry; entry_fee is in rupees.

Example

The rows must come back in the order shown.

ContestEntry

entry_iduser_identered_atentry_fee
1112024-03-02 19:05:0049
2122024-03-15 20:10:0099
3112024-04-01 18:45:0049
4132024-04-09 21:00:0025
5142024-04-30 23:59:0049
6122024-06-03 19:30:0099
7152024-07-11 20:00:0025
8112024-07-21 19:15:0049

Output

monthentriesfees_collectedchange_from_prev
2024-032148NULL
2024-0431231
2024-061991
2024-072741
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
entry_idint
user_idint
entered_atdatetime
entry_feeint

Primary key: entry_id.

One row per paid contest entry; entry_fee is in rupees.

Examples

Example 1

ContestEntry

entry_iduser_identered_atentry_fee
1112024-03-02 19:05:0049
2122024-03-15 20:10:0099
3112024-04-01 18:45:0049
4132024-04-09 21:00:0025
5142024-04-30 23:59:0049
6122024-06-03 19:30:0099
7152024-07-11 20:00:0025
8112024-07-21 19:15:0049

Output

monthentriesfees_collectedchange_from_prev
2024-032148NULL
2024-0431231
2024-061991
2024-072741

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.month

Another 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 →