Meta: Marketplace Listings Against the Same Month Last Year
Marketplace's India team reviews supply growth city by city, always against the same calendar month a year earlier so that festival seasons line up.
- Difficulty: Medium
- Topics: Dates, Aggregation, Joins
- Dialect: MySQL
- Problem: #765
Problem statement
Marketplace's India team reviews supply growth city by city, always against the same calendar month a year earlier so that festival seasons line up.
For every city and every month in which that city had at least one listing in 2025, return city, month_num (1–12), listings_2025 (listings created in that month of 2025), listings_2024 (listings in the same city and month of 2024, 0 if there were none) and difference (listings_2025 minus listings_2024). Listings from other years are ignored, and a month with listings in 2024 but none in 2025 is not returned. Return the rows in any order.
A practice scenario set at Meta: the tables and data are invented, and this is not a question from, or endorsed by, Meta.
Tables
Table: MarketListing
| Column | Type |
|---|---|
| listing_id | int |
| city | varchar |
| category | varchar |
| listed_on | date |
Primary key: listing_id.
One row per Marketplace listing, with the city it was posted in and the day it went live.
Examples
Example 1
MarketListing
| listing_id | city | category | listed_on |
|---|---|---|---|
| 1 | Pune | Furniture | 2024-03-05 |
| 2 | Pune | Bikes | 2024-03-20 |
| 3 | Pune | Furniture | 2025-03-11 |
| 4 | Pune | Electronics | 2025-03-28 |
| 5 | Pune | Books | 2025-03-30 |
| 6 | Kochi | Clothing | 2025-01-15 |
| 7 | Kochi | Bikes | 2024-02-10 |
| 8 | Delhi | Electronics | 2025-03-01 |
| 9 | Pune | Furniture | 2023-03-15 |
| 10 | Delhi | Home Decor | 2024-03-31 |
Output
| city | month_num | listings_2025 | listings_2024 | difference |
|---|---|---|---|---|
| Delhi | 3 | 1 | 1 | 0 |
| Kochi | 1 | 1 | 0 | 1 |
| Pune | 3 | 3 | 2 | 1 |
How to solve Meta: Marketplace Listings Against the Same Month Last Year
A year-over-year comparison pairs each period with the same period a year earlier, not with the previous row. Here the period is a calendar month of a city, so the first step counts listings per (city, year, month).
The 2025 rows drive the answer, which is why they are on the left of a LEFT JOIN to the 2024 rows on the same city and month. A month that existed only in 2024 has no 2025 row and is not returned; a 2025 month with no 2024 counterpart keeps its row with NULL on the right, which COALESCE(prev.n, 0) turns into the 0 the statement asks for — and the difference must be computed from that 0 as well, or it would be NULL. Rows from 2023 never match yr = 2024 and are ignored.
The alternative is a crosstab: group once by city and month over 2024–2025 and let conditional sums count each year in its own column (the difference adds +1 for a 2025 listing and −1 for a 2024 one); HAVING drops the months without a 2025 listing. It reads the table once, with no join.
Reference solution (MySQL)
WITH monthly AS (
SELECT city, YEAR(listed_on) AS yr, MONTH(listed_on) AS mon, COUNT(*) AS n
FROM MarketListing
GROUP BY city, YEAR(listed_on), MONTH(listed_on)
)
SELECT cur.city, cur.mon AS month_num, cur.n AS listings_2025,
COALESCE(prev.n, 0) AS listings_2024,
cur.n - COALESCE(prev.n, 0) AS difference
FROM monthly cur
LEFT JOIN monthly prev ON prev.city = cur.city AND prev.mon = cur.mon AND prev.yr = 2024
WHERE cur.yr = 2025Another way
SELECT city, MONTH(listed_on) AS month_num,
SUM(CASE WHEN YEAR(listed_on) = 2025 THEN 1 ELSE 0 END) AS listings_2025,
SUM(CASE WHEN YEAR(listed_on) = 2024 THEN 1 ELSE 0 END) AS listings_2024,
SUM(CASE WHEN YEAR(listed_on) = 2025 THEN 1 WHEN YEAR(listed_on) = 2024 THEN -1 ELSE 0 END) AS difference
FROM MarketListing
WHERE listed_on BETWEEN '2024-01-01' AND '2025-12-31'
GROUP BY city, MONTH(listed_on)
HAVING SUM(CASE WHEN YEAR(listed_on) = 2025 THEN 1 ELSE 0 END) > 0← Meta: Running Quest Store Revenue per Developer · Meta: WhatsApp Business Conversation Windows →