Meta: Marketplace Listings Against the Same Month Last Year

Medium
765.

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.

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

ColumnType
listing_idint
cityvarchar
categoryvarchar
listed_ondate

Primary key: listing_id.

One row per Marketplace listing, with the city it was posted in and the day it went live.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

MarketListing

listing_idcitycategorylisted_on
1PuneFurniture2024-03-05
2PuneBikes2024-03-20
3PuneFurniture2025-03-11
4PuneElectronics2025-03-28
5PuneBooks2025-03-30
6KochiClothing2025-01-15
7KochiBikes2024-02-10
8DelhiElectronics2025-03-01
9PuneFurniture2023-03-15
10DelhiHome Decor2024-03-31

Output

citymonth_numlistings_2025listings_2024difference
Delhi3110
Kochi1101
Pune3321
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.

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

ColumnType
listing_idint
cityvarchar
categoryvarchar
listed_ondate

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_idcitycategorylisted_on
1PuneFurniture2024-03-05
2PuneBikes2024-03-20
3PuneFurniture2025-03-11
4PuneElectronics2025-03-28
5PuneBooks2025-03-30
6KochiClothing2025-01-15
7KochiBikes2024-02-10
8DelhiElectronics2025-03-01
9PuneFurniture2023-03-15
10DelhiHome Decor2024-03-31

Output

citymonth_numlistings_2025listings_2024difference
Delhi3110
Kochi1101
Pune3321

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 = 2025

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