Calculating marketplace liquidity in SQL

What marketplace liquidity actually measures

Liquidity is the percentage of supply that finds demand inside a fixed time window. On Airbnb, it's the share of available listing-nights booked in a month. On eBay, the share of new listings that sell within thirty days. On Uber, the share of ride requests matched to a driver within two minutes. The unit changes; the question is always the same: when supply shows up, does the other side of the market notice?

Imagine the head of marketplace at DoorDash pings you on a Friday afternoon. Restaurant signups are up forty percent quarter over quarter, but average order value is flat and operations is asking whether the new restaurants are getting orders. The CFO wants a number by Monday. That number is liquidity, and it lives in the gap between a listing being created and a transaction closing.

Strong marketplaces hover around thirty percent listing liquidity for active categories like apartments, ride-sharing, and food delivery. Niche or premium categories run lower, often five to fifteen percent, because the median listing waits longer for the right buyer. A liquidity number in isolation tells you nothing, which is why this post pairs it with time-to-match, category drilldowns, and a supply-demand balance check.

The SQL formula

There are two complementary definitions every marketplace analyst should write from memory. Supply-side liquidity: of every listing posted in a time window, what fraction transacted? Demand-side liquidity: of every buyer who showed up with intent (a search, a contact, a request), what fraction converted into a successful match?

Both should be time-bounded. A listing that "eventually sold" two years later is not contributing to a healthy market this quarter, and pretending otherwise inflates the number to one hundred percent for any historical cohort.

Listing liquidity  = sold_listings / total_listings within window
Demand liquidity   = matched_demand / total_searches within window
Time-to-match      = median days between listed_at and sold_at

The baseline query for monthly listing liquidity over the last six months looks like this on Postgres or Snowflake:

WITH monthly_listings AS (
    SELECT
        DATE_TRUNC('month', listed_at) AS cohort_month,
        COUNT(*) AS total_listings,
        COUNT(*) FILTER (WHERE sold) AS sold_listings
    FROM listings
    WHERE listed_at >= CURRENT_DATE - INTERVAL '6 months'
    GROUP BY 1
)
SELECT
    cohort_month,
    total_listings,
    sold_listings,
    ROUND(sold_listings::NUMERIC * 100 / NULLIF(total_listings, 0), 2) AS liquidity_pct
FROM monthly_listings
ORDER BY cohort_month;

The cohort is anchored on listed_at, not on sold_at. If you anchor on the sale month, you mix listings created in different months into one bucket and lose the ability to compare cohort quality over time. Cohort-by-cohort is the only way to detect a regression in matching quality after a homepage redesign or a search-ranking change.

Time-to-match: the half-life of a listing

Liquidity tells you whether a match happens. Time-to-match tells you how fast. A market where the median listing sells in three days behaves nothing like one where the median sells in thirty, even if both report a forty percent monthly liquidity number. The first is a hot market that punishes hesitation; the second is a slow market where listings rot and sellers churn.

SELECT
    COUNT(*) FILTER (WHERE sold) AS sold_listings,
    AVG(EXTRACT(EPOCH FROM (sold_at - listed_at)) / 86400)
        FILTER (WHERE sold) AS avg_days_to_sell,
    PERCENTILE_CONT(0.5) WITHIN GROUP (
        ORDER BY EXTRACT(EPOCH FROM (sold_at - listed_at)) / 86400
    ) FILTER (WHERE sold) AS median_days,
    PERCENTILE_CONT(0.9) WITHIN GROUP (
        ORDER BY EXTRACT(EPOCH FROM (sold_at - listed_at)) / 86400
    ) FILTER (WHERE sold) AS p90_days
FROM listings
WHERE listed_at >= CURRENT_DATE - INTERVAL '90 days';

A median of seven days with a p90 of twenty-one is a healthy two-sided market. A median of thirty with a p90 of one hundred and twenty means the long tail is dragging the experience down, and supply is going stale before buyers see it. The p90 is the most useful number for sellers writing testimonials, because that's the experience the worst-served ten percent will describe.

Liquidity by category

Aggregate liquidity is almost always lying to you. A forty percent overall number can hide three categories at sixty percent and ten categories at five percent. The cold categories are where sellers get burned and churn first. The drilldown is non-negotiable for any marketplace with more than a handful of verticals.

SELECT
    category,
    COUNT(*) AS listings,
    COUNT(*) FILTER (WHERE sold) AS sold,
    ROUND(
        COUNT(*) FILTER (WHERE sold)::NUMERIC * 100 / NULLIF(COUNT(*), 0),
        2
    ) AS liquidity_pct,
    ROUND(
        AVG(EXTRACT(EPOCH FROM (sold_at - listed_at)) / 86400)
            FILTER (WHERE sold)::NUMERIC,
        1
    ) AS avg_days_to_sell
FROM listings l
JOIN products p USING (product_id)
WHERE listed_at >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY category
ORDER BY liquidity_pct DESC;

Hot categories sit at the top with five-day medians. Cold ones pile up at the bottom with sixty-day medians and single-digit liquidity. If you have a category-recommendation feature on your homepage, the cold list is your shortlist of categories to suppress until supply rebalances.

Supply and demand balance

Liquidity describes outcomes, but the leading indicator is the ratio of incoming demand to standing supply. A category where ten searches happen for every active listing is starved of supply; one where searches and listings sit one-to-one is balanced; one where listings outnumber searches three-to-one is in oversupply, and prices are about to drop.

WITH supply AS (
    SELECT category, COUNT(*) AS active_listings
    FROM listings
    WHERE listed_at >= CURRENT_DATE - INTERVAL '7 days'
      AND NOT sold
    GROUP BY category
),
demand AS (
    SELECT category, COUNT(*) AS searches
    FROM search_logs
    WHERE searched_at >= CURRENT_DATE - INTERVAL '7 days'
    GROUP BY category
)
SELECT
    s.category,
    s.active_listings,
    d.searches,
    ROUND(d.searches::NUMERIC / NULLIF(s.active_listings, 0), 2) AS demand_per_listing,
    CASE
        WHEN d.searches::NUMERIC / NULLIF(s.active_listings, 0) > 10 THEN 'high demand, recruit supply'
        WHEN d.searches::NUMERIC / NULLIF(s.active_listings, 0) > 3  THEN 'balanced'
        ELSE 'oversupply'
    END AS market_state
FROM supply s
JOIN demand d USING (category)
ORDER BY demand_per_listing DESC;

This is the dashboard the growth team should be staring at every Monday. The "recruit supply" rows are where ad spend on the supplier side has the highest return — demand is already there waiting. The "oversupply" rows are where you stop spending on sellers and reroute budget to buyer-side acquisition or pricing experiments.

Two-sided liquidity in one query

If you only have room for one chart on the executive dashboard, this is it. Supply-side liquidity: percentage of listings that closed a sale in the last thirty days. Demand-side liquidity: percentage of search sessions that ended in a purchase in the same window.

WITH supply_side AS (
    SELECT
        COUNT(*) AS total_listings,
        COUNT(*) FILTER (WHERE sold) AS sold_listings,
        ROUND(
            COUNT(*) FILTER (WHERE sold)::NUMERIC * 100 / NULLIF(COUNT(*), 0),
            2
        ) AS supply_liquidity_pct
    FROM listings
    WHERE listed_at >= CURRENT_DATE - INTERVAL '30 days'
),
demand_side AS (
    SELECT
        COUNT(*) AS total_searches,
        COUNT(*) FILTER (WHERE resulted_in_purchase) AS converted_searches,
        ROUND(
            COUNT(*) FILTER (WHERE resulted_in_purchase)::NUMERIC * 100 / NULLIF(COUNT(*), 0),
            2
        ) AS demand_liquidity_pct
    FROM search_logs
    WHERE searched_at >= CURRENT_DATE - INTERVAL '30 days'
)
SELECT
    s.supply_liquidity_pct AS listing_liquidity_pct,
    d.demand_liquidity_pct AS search_to_purchase_pct
FROM supply_side s, demand_side d;

Healthy marketplaces have both numbers moving together. If supply liquidity climbs while demand liquidity stays flat, you're matching listings to a shrinking pool of buyers who already know what they want, and you're about to hit a ceiling. If demand liquidity climbs while supply drops, you have more buyers than your sellers can serve, and the tail of search results is filling with low-quality listings.

Common pitfalls

When teams calculate liquidity for the first time, the most common mistake is leaving out the time window. A query that says "fraction of all listings that ever sold" will look great for two-year-old cohorts and terrible for last week's, which inverts the actual health signal. The fix is to fix the window at thirty or ninety days and report cohort by cohort so newer cohorts are compared against older ones at the same age.

A second trap is conflating sold with delisted. A listing the seller pulled because they sold off-platform is not the same as one that found a buyer through the marketplace. The first counts as a missed match; the second counts as revenue. Stripe and Airbnb both maintain separate event types for these states. If your warehouse only has a boolean sold flag, ask engineering to add delisted_reason before you publish a liquidity number to leadership.

A third pitfall is aggregating across categories. A blended forty percent number can hide three high-volume categories at sixty percent and a long tail at five percent. The cold tail is where sellers churn first, and the average will not warn you. Always pair an overall number with a drilldown sorted ascending, and put the bottom five categories on the dashboard.

A fourth trap is ignoring the demand side. Supply liquidity at fifty percent looks healthy until you discover that demand liquidity is at five percent because buyers are searching for things you don't carry. Track both numbers on the same chart, and add a search-without-result rate as a tertiary metric.

A fifth pitfall is price effects. Heavily discounted listings have artificially high liquidity, and a marketplace optimizing only for liquidity will quietly pressure sellers into a race to the bottom. Track liquidity weighted by gross merchandise value, not listing count, when reporting to anyone who can change pricing policy.

Optimization tips

Cohort queries that scan every row in a multi-billion-row listings table will time out on Postgres long before they finish on Snowflake or BigQuery. Partition by listed_at so the planner can prune to the last six months without a full scan. On Databricks Delta tables, set OPTIMIZE with ZORDER BY (listed_at, category) to keep file pruning effective when queries drill down by both axes.

The FILTER clause is standard ANSI SQL and runs faster than wrapping the same aggregation in a CASE WHEN because the planner recognizes it as a sparse aggregate. If your warehouse predates the standard, fall back to SUM(CASE WHEN sold THEN 1 ELSE 0 END) and accept the worse plan. On Snowflake and BigQuery, prefer FILTER everywhere.

For dashboards refreshing hourly, build a small daily summary table keyed by (cohort_month, category) with raw counts pre-aggregated. The dashboard then reads a few thousand rows instead of recomputing from raw listings every refresh — a materialized view on Snowflake, a scheduled query on BigQuery, or a cron-driven REFRESH MATERIALIZED VIEW on Postgres.

Related reading

• SQL window functions interview questions
• How to calculate GMV in SQL
• How to calculate fill rate in SQL
• How to calculate conversion uplift in SQL

If you want to drill marketplace-style SQL questions like this every day, NAILDD is launching with hundreds of SQL problems built around exactly this kind of two-sided market reasoning.

FAQ

What's a healthy marketplace liquidity number?

For active marketplaces with frequent transactions, anything above thirty percent monthly listing liquidity is healthy. Airbnb publishes thirty to fifty percent depending on the city. Niche marketplaces in collectibles or industrial equipment run ten to twenty percent because the right buyer takes longer to find. Premium or one-of-a-kind items can be considered healthy at five to fifteen percent, especially when paired with a high gross merchandise value per match.

How is marketplace liquidity different from conversion rate?

Conversion rate is funnel-shaped and buyer-centric: percentage of buyer sessions that turn into a purchase. Marketplace liquidity is listing-centric: percentage of supply that finds demand within a window. A marketplace can have high conversion (motivated buyers) and low liquidity (most listings sit idle) — the classic long-tail problem.

How do you improve liquidity?

Five levers consistently work. Invest in matching and search relevance so supply is shown to the right buyers. Ship pricing recommendations so listings start at a market-clearing price. Enforce a minimum quality bar on photos and descriptions. Recruit demand through marketing into categories where supply is already strong. Subsidize one side of the market on cold-start days, the way Airbnb paid for professional photos of new listings in 2010.

How long should the cohort window be?

For high-frequency marketplaces like ride-sharing or food delivery, seven to thirty days is enough to read trends. For deliberate-purchase marketplaces like apartments or used cars, ninety days is the right window — buyers shop slowly. For long-tail marketplaces like collectibles or industrial gear, a year is sometimes the only window where the number stabilizes. Pick the window once and lock it in across dashboards.

How do you handle the cold-start problem for a brand-new marketplace?

A new marketplace has empty supply and empty demand, and reporting zero percent liquidity does not help. Pick the side of the market with higher long-term value (usually supply) and subsidize it directly until the other side organically grows. Airbnb sent photographers to apartments in New York. Uber paid drivers a guaranteed hourly minimum in their first market. DoorDash hand-delivered orders in Palo Alto before opening up to couriers.

Should liquidity be weighted by gross merchandise value?

For executive dashboards, yes. Unweighted liquidity treats a one-dollar sticker and a thousand-dollar bicycle as identical events, which underweights the categories that drive revenue. Report both numbers: unweighted for matching-quality conversations, GMV-weighted for revenue conversations. If they diverge sharply, low-value listings dominate match volume and the marketplace is healthy on paper but not in cash.