Как посчитать Liquidity на marketplace в SQL

Зачем Liquidity

Liquidity marketplace — % listings, нашедшие buyer (или vice versa). Hallmark healthy marketplace. Airbnb 30%+ listings booked monthly. eBay — high liquidity для popular, low для rare items.

Формула

Listing Liquidity = sold_listings / total_listings × 100%
Demand Liquidity = matched_demand / total_searches × 100%

Time-bounded (per month, per week).

Базовый расчёт

WITH monthly_listings AS (
    SELECT
        DATE_TRUNC('month', listed_at) AS 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
    month,
    total_listings,
    sold_listings,
    sold_listings::NUMERIC * 100 / NULLIF(total_listings, 0) AS liquidity_pct
FROM monthly_listings
ORDER BY month;

Time-to-match

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

Median 7 days = liquid market. > 30 days = illiquid.

По категориям

SELECT
    category,
    COUNT(*) AS listings,
    COUNT(*) FILTER (WHERE sold) AS sold,
    COUNT(*) FILTER (WHERE sold)::NUMERIC * 100 / COUNT(*) AS liquidity_pct,
    AVG(EXTRACT(EPOCH FROM (sold_at - listed_at)) / 86400) FILTER (WHERE sold) 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 sell fast. Cold ones — inventory glut.

Supply / demand balance

WITH supply AS (
    SELECT category, COUNT(*) AS 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.listings,
    d.searches,
    d.searches::NUMERIC / NULLIF(s.listings, 0) AS demand_per_listing,
    CASE
        WHEN d.searches::NUMERIC / NULLIF(s.listings, 0) > 10 THEN 'high demand (need more supply)'
        WHEN d.searches::NUMERIC / NULLIF(s.listings, 0) > 3 THEN 'balanced'
        ELSE 'oversupply'
    END AS status
FROM supply s
JOIN demand d USING (category)
ORDER BY demand_per_listing DESC;

Two-sided liquidity

Supply: % listed items sold. Demand: % searches resulted в purchase:

WITH supply_side AS (
    SELECT
        COUNT(*) AS total_listings,
        COUNT(*) FILTER (WHERE sold) AS sold,
        COUNT(*) FILTER (WHERE sold)::NUMERIC * 100 / COUNT(*) AS supply_liquidity
    FROM listings
    WHERE listed_at >= CURRENT_DATE - INTERVAL '30 days'
),
demand_side AS (
    SELECT
        COUNT(*) AS searches,
        COUNT(*) FILTER (WHERE resulted_in_purchase) AS converted,
        COUNT(*) FILTER (WHERE resulted_in_purchase)::NUMERIC * 100 / COUNT(*) AS demand_liquidity
    FROM search_logs
    WHERE searched_at >= CURRENT_DATE - INTERVAL '30 days'
)
SELECT
    supply.supply_liquidity AS listing_liquidity,
    demand.demand_liquidity AS search_to_purchase
FROM supply_side supply, demand_side demand;

Частые ошибки

Ошибка 1. Liquidity without time bound. «Listing eventually sold» — always 100% if waited forever. Use 30 / 90 day windows.

Ошибка 2. Sold vs delisted. Listing sold (revenue) vs delisted (no buyer found, returned to seller). Different.

Ошибка 3. Aggregate hides cold categories. Average 40% liquidity, но 10% categories at 5%. Drill.

Ошибка 4. Both sides ignored. Supply liquidity 50%, но demand liquidity 5%. Buyers поисковая frustrated.

Ошибка 5. Price effects. Cheap listings — high liquidity. Quality is separate dimension.

Связанные темы

• Как посчитать GMV в SQL
• Как посчитать take rate в SQL
• Как посчитать conversion uplift в SQL
• Как посчитать fill rate в SQL

FAQ

Какой Liquidity ok?

Active marketplace: 30%+. Niche: 10-20%. Premium / unique items: 5-15%.

Improve liquidity как?

1. Match algorithm (relevance). 2) Pricing recommendations. 3) Photos / descriptions. 4) Bring more demand (marketing). 5) Subsidies для new sellers / buyers.

Liquidity per category?

Yes. Different markets — different baseline.

Cold start problem?

New marketplace — both sides empty. Subsidize one side first (Airbnb hosts с photo помогли).

Liquidity vs Conversion?

Liquidity — listing-level. Conversion — funnel (search → purchase). Related но different.