Investigate a Revenue Drop with SQL: A Worked Example

By Prakhar Shrivastava · Worked practice example

Revenue is down, but traffic looks unchanged. A useful investigation starts by defining the numbers, then locating the change. This original practice case uses a synthetic dataset and SQLite queries you can run. It is not a report about a real company or a verified employer interview question.

Define the comparison before writing SQL

Assume each date below labels a complete seven-day week in the same timezone. Each row is one acquisition channel for one week. Revenue uses the same currency and definition in both periods. Sessions and orders are assigned to exactly one channel. Those assumptions matter: overlapping attribution or an incomplete current week would make the comparison misleading.

In a production investigation, clarify whether revenue means gross sales, paid orders, or net revenue after refunds. Confirm how cancelled orders are counted. Record the extraction time and the source of each metric so a colleague can reproduce the calculation.

Create the small dataset

CREATE TABLE weekly_metrics (
  week TEXT, channel TEXT, sessions INTEGER,
  orders INTEGER, revenue REAL,
  PRIMARY KEY (week, channel)
);
INSERT INTO weekly_metrics VALUES
('2026-08-03','organic',1000,50,5000),
('2026-08-03','paid',1000,40,4000),
('2026-08-03','email',500,50,5000),
('2026-08-10','organic',1100,55,5500),
('2026-08-10','paid',1000,20,2000),
('2026-08-10','email',400,40,4000);

Calculate the totals and rates

SELECT week, SUM(sessions) AS sessions,
       SUM(orders) AS orders, SUM(revenue) AS revenue,
       ROUND(100.0 * SUM(orders) / NULLIF(SUM(sessions),0),2)
         AS conversion_pct,
       ROUND(SUM(revenue) / NULLIF(SUM(orders),0),2) AS aov
FROM weekly_metrics
GROUP BY week
ORDER BY week;

week        sessions orders revenue conversion_pct aov
2026-08-03  2500     140    14000   5.6            100
2026-08-10  2500     115    11500   4.6            100

Revenue declined by 2,500, or 17.86% of the baseline 14,000. Sessions stayed at 2,500. Conversion fell from 5.6% to 4.6%: a drop of one percentage point, not one percent. Average order value stayed at 100. The arithmetic therefore points to fewer orders per session, rather than smaller average orders, as the aggregate explanation.

Calculate rates from summed numerators and denominators. Averaging the three channel conversion rates would give each channel equal weight despite different session counts. NULLIF prevents division by zero; a missing rate should remain distinguishable from a measured zero.

Locate the largest contribution

SELECT current.channel,
       current.revenue - baseline.revenue AS revenue_change,
       current.orders - baseline.orders AS order_change
FROM weekly_metrics AS current
JOIN weekly_metrics AS baseline
  ON current.channel = baseline.channel
WHERE current.week = '2026-08-10'
  AND baseline.week = '2026-08-03'
ORDER BY revenue_change;

channel  revenue_change  order_change
paid     -2000           -20
email    -1000           -10
organic    500             5

The channel changes reconcile to the total decline: −2,000 −1,000 +500 = −2,500. Paid traffic is the first segment to investigate because its revenue loss is largest in absolute terms. Email also contributes; organic partly offsets the losses. Saying paid accounts for 80% of the net decline describes this arithmetic, not a causal attribution model.

Separate a finding from a hypothesis

Paid sessions are unchanged while paid orders halve. Possible explanations include a change in visitor intent, a landing-page issue, checkout friction, or missing order tracking. This table cannot choose between them. Compare the order source with the payment system, check campaign and release logs, then segment by device or landing page if those fields exist. Do not invent a segment breakdown from unavailable data.

The join above is valid because this exercise gives both weeks the same channel set. In real data, a channel may appear in only one period. Build a complete channel spine or use a suitable outer-join approach so new and discontinued channels are retained.

An interview answer you can adapt

“Across two complete comparable weeks, revenue fell 17.86% while sessions and average order value were stable. Paid-channel orders account for the largest absolute loss. I would first reconcile payment and tracking data, then inspect paid landing pages and device segments. I cannot conclude that an advertising change caused the decline from these aggregates alone.”

Practise an edge case

Add a new channel in the current week and rerun the second query. Notice that it disappears because there is no baseline match. Rewrite the comparison with conditional aggregation over all channels, and check that channel differences still sum to the overall difference. This is a useful way to test whether your query reflects the business question.

Continue with the SQL learning hub. Reference: SQLite aggregate-function documentation.