Meta: Marketplace Listings Sold Within a Day

Easy
748.

Meta: Marketplace Listings Sold Within a Day

EasyDatesBasics

MySQL

A product analyst on Facebook Marketplace is studying which listings sell fast, to decide what the "sells quickly" badge should be based on.

Return every listing that was sold within 24 hours of being listed — sold_at is at most 24 hours after listed_at, and a sale at exactly 24 hours still counts. Unsold listings (sold_at NULL) never qualify. Return the columns listing_id, category and hours_to_sell, the whole hours between listing and sale (rounded down, so 8 h 30 min is 8), 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: MarketplaceListing

ColumnType
listing_idint
seller_idint
categoryvarchar
listed_atdatetime
sold_atdatetime
priceint

Primary key: listing_id.

One row per listing. sold_at is NULL while the item is unsold; when set it is never before listed_at. price is in rupees.

Example

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

MarketplaceListing

listing_idseller_idcategorylisted_atsold_atprice
111Furniture2025-05-01 10:00:002025-05-01 18:30:004500
212Electronics2025-05-01 09:00:002025-05-02 09:00:0012000
313Bikes2025-05-02 20:15:002025-05-03 20:16:007000
411Furniture2025-05-03 08:00:00NULL2500
514Clothing2025-05-03 12:00:002025-05-03 12:40:00800
615Electronics2025-05-04 07:00:002025-05-09 11:00:0030000

Output

listing_idcategoryhours_to_sell
1Furniture8
2Electronics24
5Clothing0
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 Sold Within a Day — SQL Problem

A product analyst on Facebook Marketplace is studying which listings sell fast, to decide what the "sells quickly" badge should be based on.

  • Difficulty: Easy
  • Topics: Dates, Basics
  • Dialect: MySQL
  • Problem: #748

Problem statement

A product analyst on Facebook Marketplace is studying which listings sell fast, to decide what the "sells quickly" badge should be based on.

Return every listing that was sold within 24 hours of being listed — sold_at is at most 24 hours after listed_at, and a sale at exactly 24 hours still counts. Unsold listings (sold_at NULL) never qualify. Return the columns listing_id, category and hours_to_sell, the whole hours between listing and sale (rounded down, so 8 h 30 min is 8), 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: MarketplaceListing

ColumnType
listing_idint
seller_idint
categoryvarchar
listed_atdatetime
sold_atdatetime
priceint

Primary key: listing_id.

One row per listing. sold_at is NULL while the item is unsold; when set it is never before listed_at. price is in rupees.

Examples

Example 1

MarketplaceListing

listing_idseller_idcategorylisted_atsold_atprice
111Furniture2025-05-01 10:00:002025-05-01 18:30:004500
212Electronics2025-05-01 09:00:002025-05-02 09:00:0012000
313Bikes2025-05-02 20:15:002025-05-03 20:16:007000
411Furniture2025-05-03 08:00:00NULL2500
514Clothing2025-05-03 12:00:002025-05-03 12:40:00800
615Electronics2025-05-04 07:00:002025-05-09 11:00:0030000

Output

listing_idcategoryhours_to_sell
1Furniture8
2Electronics24
5Clothing0

How to solve Meta: Marketplace Listings Sold Within a Day

Each listing is judged on its own, so this is a filter over one table. The deadline differs per row — it is that listing's listed_at plus 24 hours — which is why the comparison uses DATE_ADD(listed_at, INTERVAL 24 HOUR) rather than a fixed date. A sale at exactly the deadline is "within a day", so the test is sold_at <= the deadline, not <.

Unsold listings have sold_at NULL. Any comparison with NULL is unknown, so WHERE would drop them anyway; the explicit sold_at IS NOT NULL just says so. The alternative relies on that: TIMESTAMPDIFF(SECOND, listed_at, sold_at) is NULL for an unsold item and the <= 86400 test removes it.

For the reported value, TIMESTAMPDIFF(HOUR, …) counts complete hours, so 8 h 30 min reads 8 and 40 minutes reads 0; the alternative gets the same number as whole minutes divided by 60 with DIV. The query is one scan of the table.

Reference solution (MySQL)

SELECT listing_id, category, TIMESTAMPDIFF(HOUR, listed_at, sold_at) AS hours_to_sell
FROM MarketplaceListing
WHERE sold_at IS NOT NULL
  AND sold_at <= DATE_ADD(listed_at, INTERVAL 24 HOUR)

Another way

SELECT listing_id, category, TIMESTAMPDIFF(MINUTE, listed_at, sold_at) DIV 60 AS hours_to_sell FROM MarketplaceListing WHERE TIMESTAMPDIFF(SECOND, listed_at, sold_at) <= 86400

← LinkedIn: Boomerang Hires in Members' Work History · Meta: Verified WhatsApp Businesses With No Live Catalog Items →