Business Analyst SQL Questions: Five Complete Decision Cases

Five business-analyst SQL cases with decision limits

A useful business-analyst answer connects a clear metric to a decision and states what the data cannot establish. These five PostgreSQL 16 cases use complete fictional VALUES fixtures. Each includes an executable answer, expected output, and a stakeholder follow-up. They cover campaign efficiency, inventory, inactivity, product margin, and basket associations.

1. Compare attributed campaign revenue with spending

Define the unit as one campaign. Costs and attributed sales are separate facts and must be aggregated separately before joining. The fixture uses a complete attribution feed, but attributed revenue is not necessarily revenue caused by the campaign.

WITH spend(campaign,amount) AS (VALUES ('A',20::numeric),('A',30),('B',0)),
revenue(campaign,amount) AS (VALUES ('A',100::numeric),('B',10)),
c AS (SELECT campaign,SUM(amount) AS cost FROM spend GROUP BY campaign),
r AS (SELECT campaign,SUM(amount) AS revenue FROM revenue GROUP BY campaign)
SELECT c.campaign,c.cost,COALESCE(r.revenue,0) AS attributed_revenue,
 ROUND(COALESCE(r.revenue,0)/NULLIF(c.cost,0),2) AS roas
FROM c LEFT JOIN r USING(campaign) ORDER BY c.campaign;

campaign | cost | attributed_revenue | roas
A | 50 | 100 | 2.00
B | 0 | 10 | NULL

Campaign B’s zero cost makes the ratio undefined, not infinitely successful. Ask how attribution windows, refunds, shared conversions, and non-ad costs are handled. Comparing campaign-month sales with the previous month alone does not isolate campaign impact; seasonality and other changes can explain the difference.

2. Flag low stock only when the snapshot is fresh

WITH params(as_of) AS (VALUES (DATE '2026-10-17')),
products(id,min_stock) AS (VALUES (1,5),(2,5),(3,5)),
snapshots(product_id,snapshot_date,on_hand) AS (
 VALUES (1,DATE '2026-10-16',3),(1,DATE '2026-10-18',999),
 (3,DATE '2026-10-10',1)
), ranked AS (
 SELECT s.*,ROW_NUMBER() OVER(PARTITION BY product_id ORDER BY snapshot_date DESC) AS rn
 FROM snapshots s CROSS JOIN params p WHERE s.snapshot_date<p.as_of
)
SELECT pr.id,s.on_hand,s.snapshot_date,
 CASE WHEN s.product_id IS NULL THEN 'unknown'
 WHEN s.snapshot_date<p.as_of-3 THEN 'stale'
 WHEN s.on_hand<pr.min_stock THEN 'below_minimum' ELSE 'at_or_above_minimum' END AS status
FROM products pr CROSS JOIN params p
LEFT JOIN ranked s ON s.product_id=pr.id AND s.rn=1 ORDER BY pr.id;

id | on_hand | snapshot_date | status
1 | 3 | 2026-10-16 | below_minimum
2 | NULL | NULL | unknown
3 | 1 | 2026-10-10 | stale

The input contract permits one authoritative snapshot per product and snapshot_date, so date uniquely orders each product’s history. If the source supplies multiple versions for a day, choose a version with a documented unique sequence before this query. The future snapshot is excluded. Fresh means at most three days old under this example’s policy. Product 1 needs replenishment review; product 2 needs data, and product 3 needs a fresh snapshot. The query does not calculate an optimal reorder quantity: that requires lead time, demand uncertainty, incoming stock, and service objectives.

3. Find prior customers inactive in the last complete month

The baseline is customers who bought during June–August 2026. The outcome is whether they bought during the complete month of September. This avoids labeling a customer inactive based on a partially observed October.

WITH purchases(customer_id,bought_on) AS (
 VALUES (1,DATE '2026-06-10'),(1,DATE '2026-09-02'),
 (2,DATE '2026-08-20'),(3,DATE '2026-09-15')
), baseline AS (
 SELECT DISTINCT customer_id FROM purchases
 WHERE bought_on>=DATE '2026-06-01' AND bought_on<DATE '2026-09-01'
), flags AS (
 SELECT b.customer_id,NOT EXISTS(SELECT 1 FROM purchases p
  WHERE p.customer_id=b.customer_id AND p.bought_on>=DATE '2026-09-01'
  AND p.bought_on<DATE '2026-10-01') AS inactive FROM baseline b
)
SELECT COUNT(*) AS baseline_customers,COUNT(*) FILTER(WHERE inactive) AS inactive_customers,
 ROUND(100.0*COUNT(*) FILTER(WHERE inactive)/NULLIF(COUNT(*),0),2) AS inactive_pct
FROM flags;

baseline_customers | inactive_customers | inactive_pct
2 | 1 | 50.00

Customer 3 is new in September and does not belong to the baseline. Customer 2 is inactive under this purchase-window definition, not necessarily permanently churned. Ask whether returns, subscription status, test accounts, and identity changes affect eligibility. A completely empty baseline produces an undefined percentage, which should remain visible.

4. Rank products by contribution after line discounts

WITH lines(product,qty,unit_price,unit_cost,line_discount) AS (
 VALUES ('A',2,10::numeric,6,0),('A',3,10,6,3),('B',1,30,25,0)
), totals AS (
 SELECT product,SUM(qty*unit_price-line_discount) AS revenue,
 SUM(qty*unit_price-line_discount-qty*unit_cost) AS contribution
 FROM lines GROUP BY product
)
SELECT product,revenue,contribution,
 ROUND(100*contribution/NULLIF(revenue,0),2) AS contribution_pct,
 RANK() OVER(ORDER BY contribution DESC) AS contribution_rank
FROM totals ORDER BY contribution_rank,product;

product | revenue | contribution | contribution_pct | contribution_rank
A | 47 | 17 | 36.17 | 1
B | 30 | 5 | 16.67 | 2

line_discount is the whole-line discount, while price and cost are per unit. Applying the discount once per unit would misstate revenue. This measure excludes overhead, tax, shipping, and other costs, so label it contribution under this contract rather than complete net profit. Rankings by contribution amount and percentage can lead to different decisions.

5. Count product pairs without duplicate lines

WITH lines(order_id,product) AS (
 VALUES (1,'A'),(1,'A'),(1,'B'),(2,'A'),(2,'B'),(3,'A'),(3,'C')
), basket AS (SELECT DISTINCT order_id,product FROM lines),
pairs AS (
 SELECT a.order_id,a.product AS product_a,b.product AS product_b
 FROM basket a JOIN basket b ON a.order_id=b.order_id AND a.product<b.product
), denominator AS (SELECT COUNT(DISTINCT order_id) AS orders FROM basket)
SELECT product_a,product_b,COUNT(*) AS paired_orders,
 ROUND(100.0*COUNT(*)/(SELECT orders FROM denominator),2) AS support_pct
FROM pairs GROUP BY product_a,product_b ORDER BY product_a,product_b;

product_a | product_b | paired_orders | support_pct
A | B | 2 | 66.67
A | C | 1 | 33.33

The distinct order-product step prevents order 1’s repeated A line from counting twice. The inequality keeps each unordered pair once. The denominator is the three represented orders, not all possible pairs. An order with no item rows would need an order-table denominator if it belongs in the population. Co-purchase association does not estimate how sales would change if one product went out of stock.

Explain the next decision and missing evidence

For each case, present the metric, the cutoff, the result, and one limitation that changes the action. For example: “Product 1 is below its agreed minimum on a fresh snapshot; I would check incoming orders and lead time before choosing a replenishment quantity.” Avoid treating a plausible-looking number as a complete recommendation.

Recheck the fixtures with zero denominators, repeated events, missing snapshots, and boundary timestamps. Keep each question’s unit clear: campaign, product snapshot, customer cohort, product contribution, or order pair. For a longer integrated exercise, use the retail SQL case study. Primary references: conditional expressions, windows, and existence tests.