Meesho hard JOINCASE WHENGROUP BYHAVINGDENSE_RANKWindow FunctionsRunning Total

For each reseller, calculate net revenue as the total value of delivered orders minus the total value of RTO (returned) orders. Only include resellers with at least 5 total orders. Rank resellers within their product category by net revenue, using DENSE_RANK so tied resellers share a rank. Also calculate the running total of net revenue within each category, ordered by rank (and by reseller name to break ties consistently). Return reseller_name, category, total_orders, net_revenue, category_rank, and running_net_revenue, sorted by category, then rank, then reseller name.

Meesho · Reseller Analytics — practice this real-world SQL scenario live in your browser.

📖 Story Meesho · Reseller Analytics

Thursday afternoon at Meesho's Reseller Growth team. COD orders that get returned before delivery — RTO, or Return to Origin — eat directly into reseller margins, and leadership wants to identify which resellers are actually profitable once RTO losses are subtracted, not just which ones have the most orders. Small or new resellers with only a handful of orders would skew the comparison, so they need to be excluded first.

🎯 Your Mission

📋 Table Structure

🗂 resellers

reseller_id INTEGER 1

reseller_name TEXT Anjali Traders

category TEXT Sarees

city TEXT Surat

🗂 orders

order_id INTEGER 1

reseller_id INTEGER 1

order_amount REAL 1200

status TEXT delivered

⚡ Step-by-Step Walkthrough

Calculate total orders and net revenue per reseller, filtering out low-volume resellers

query.sql

SELECT r.reseller_id,
       r.reseller_name,
       r.category,
       COUNT(o.order_id) AS total_orders,
       SUM(CASE WHEN o.status = 'delivered' THEN o.order_amount ELSE 0 END)
         - SUM(CASE WHEN o.status = 'rto' THEN o.order_amount ELSE 0 END) AS net_revenue
FROM resellers r
JOIN orders o ON o.reseller_id = r.reseller_id
GROUP BY r.reseller_id, r.reseller_name, r.category
HAVING COUNT(o.order_id) >= 5
ORDER BY r.category, net_revenue DESC;

💡 Explanation

The orders table has one row per order, tagged 'delivered' or 'rto'. To get net revenue per reseller, delivered and RTO amounts need to be summed separately, then subtracted — a single SUM(order_amount) would just add RTO losses on top of revenue instead of subtracting them.
SUM(CASE WHEN status='delivered' THEN order_amount ELSE 0 END) adds up only delivered orders; the matching CASE for 'rto' adds up only returned orders. Subtracting the second from the first gives net revenue — money actually kept after returns.
HAVING COUNT(order_id) >= 5 removes resellers with fewer than 5 total orders, after grouping — HAVING filters on the aggregated result, unlike WHERE, which would run before COUNT exists to filter on.
Meena Boutique has only 3 orders and is correctly excluded here. Priya Fashions has exactly 5 — the boundary — and is correctly included, since the requirement is 'at least 5,' not 'more than 5.'

Rank resellers within their category by net revenue

query.sql

WITH reseller_totals AS (
  SELECT r.reseller_id,
         r.reseller_name,
         r.category,
         COUNT(o.order_id) AS total_orders,
         SUM(CASE WHEN o.status = 'delivered' THEN o.order_amount ELSE 0 END)
           - SUM(CASE WHEN o.status = 'rto' THEN o.order_amount ELSE 0 END) AS net_revenue
  FROM resellers r
  JOIN orders o ON o.reseller_id = r.reseller_id
  GROUP BY r.reseller_id, r.reseller_name, r.category
  HAVING COUNT(o.order_id) >= 5
)
SELECT reseller_name,
       category,
       total_orders,
       net_revenue,
       DENSE_RANK() OVER (PARTITION BY category ORDER BY net_revenue DESC) AS category_rank
FROM reseller_totals
ORDER BY category, category_rank;

💡 Explanation

The previous step's result becomes a CTE (reseller_totals) so it can be reused without repeating the whole GROUP BY/HAVING query — DENSE_RANK needs to run on top of the already-aggregated net revenue, not on raw order rows.
DENSE_RANK() OVER (PARTITION BY category ORDER BY net_revenue DESC) ranks resellers separately within each category — Sarees, Kurtis, and Jewellery each get their own rank 1, restarting for every category.
DENSE_RANK specifically (not RANK) matters here because of the tie: Anjali Traders and Priya Fashions both land on 3300 net revenue in Sarees. DENSE_RANK gives them both rank 1 without leaving a gap afterward — RANK would also give them both rank 1, but the next Sarees reseller would jump to rank 3, misrepresenting how many distinct performance tiers actually exist.
Check the Jewellery and Kurtis categories: each has a clean rank 1 and rank 2, no ties, confirming the ranking logic works whether or not a tie is present.

Add the running total and finalize the output

query.sql

WITH reseller_totals AS (
  SELECT r.reseller_id,
         r.reseller_name,
         r.category,
         COUNT(o.order_id) AS total_orders,
         SUM(CASE WHEN o.status = 'delivered' THEN o.order_amount ELSE 0 END)
           - SUM(CASE WHEN o.status = 'rto' THEN o.order_amount ELSE 0 END) AS net_revenue
  FROM resellers r
  JOIN orders o ON o.reseller_id = r.reseller_id
  GROUP BY r.reseller_id, r.reseller_name, r.category
  HAVING COUNT(o.order_id) >= 5
)
SELECT reseller_name,
       category,
       total_orders,
       net_revenue,
       DENSE_RANK() OVER (PARTITION BY category ORDER BY net_revenue DESC) AS category_rank,
       SUM(net_revenue) OVER (
         PARTITION BY category
         ORDER BY net_revenue DESC, reseller_name
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_net_revenue
FROM reseller_totals
ORDER BY category, category_rank, reseller_name;

💡 Explanation

SUM(net_revenue) OVER (PARTITION BY category ORDER BY net_revenue DESC, reseller_name ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) adds up net revenue cumulatively within each category, in rank order — each row's running total is the sum of its own net revenue plus every higher-ranked reseller's net revenue in that category.
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW explicitly defines the window frame as 'from the start of this category's partition up to the current row' — SQLite's default frame would do the same thing here, but writing it explicitly avoids relying on default behavior that differs across databases.
The ORDER BY inside this window function includes reseller_name as a tiebreaker, not just net_revenue DESC. Without it, when two rows tie (Anjali Traders and Priya Fashions, both 3300), the running total would still be correct as a final number, but the order the tied rows are processed in — and therefore which one shows 3300 and which shows 6600 first — would be undefined, exactly the non-deterministic-ORDER BY problem that causes results to shift between runs on identical data.
The final SELECT's own ORDER BY (category, category_rank, reseller_name) is separate from the window function's internal ORDER BY — one controls calculation order inside each partition, the other controls how the finished rows are displayed. They happen to use the same columns here, but they are not the same clause and don't have to match.

▶ Practice this live ← Browse all questions