SQL interview question: find the bugs in a revenue query

MediumCode review

The dashboard shows more revenue than the bank statement. For each city and platform, find revenue from successful September 2026 payments for delivered orders. Also give the platform’s share of the city’s revenue.

• revenue_share = revenue_usd / the whole city’s revenue

WITH city_revenue AS (
  SELECT city,
         SUM(amount_usd) AS city_revenue_usd
  FROM orders
  WHERE created_at >= '2026-09-01'
    AND created_at < '2026-10-01'
  GROUP BY city
)
SELECT o.city,
       o.platform,
       COUNT(*) AS orders,
       SUM(p.amount_usd) AS revenue_usd,
       ROUND(SUM(p.amount_usd)::numeric / MAX(cr.city_revenue_usd), 4) AS revenue_share
FROM payments AS p
JOIN orders AS o
  ON o.order_id = p.order_id
JOIN city_revenue AS cr
  ON cr.city = o.city
WHERE o.status = 'delivered'
  AND p.paid_at >= '2026-09-01'
  AND p.paid_at < '2026-10-01'
GROUP BY o.city, o.platform

Example

Boston’s revenue is $24 + $16 = $40: ios gets 0.6 and web 0.4.

Output columns

orders

number of orders with a successful payment

Skills it tests

• Reading SQL
• Joins
• Revenue shares

More interview questions

• SQL: Second-highest price in each groupEasy · Nth-highest value
• SQL: Top 3 categories in each cityMedium · Top-N per group
• SQL: Day-1 retention by channelMedium · Retention
• SQL: Find the bugs in a churn queryMedium · Code review
• Python: Find the bugs in a median functionEasy · Code review

All questions