# Consistent High-Quantity Revenue

> Big orders, consistent revenue. A rare combination.

Canonical URL: <https://datadriven.io/problems/consistent_high_quantity_revenue>

Domain: SQL · Difficulty: medium · Seniority: L4

## Problem

Compute total transaction revenue for each user and product pair, but only for pairs where every single transaction had a quantity of at least 2. If any transaction for that pair falls below 2, exclude the entire pair. Show user, product, and total revenue, listed by user ascending and revenue descending.

## Worked solution and explanation

### Why this problem exists in real interviews

This tests a universal quantifier pattern: "every transaction meets condition X." SQL lacks a direct FORALL, so interviewers check whether you can express it using HAVING with MIN, NOT EXISTS, or conditional counts.

> **Trick to Solving**
>
> "Every single transaction had a quantity of at least 2" is a universal quantifier. The cleanest SQL pattern is `HAVING MIN(quantity) >= 2`, which returns true only if all rows in the group meet the condition.
> 
> 1. Group by (user_id, product_id)
> 2. Check MIN(quantity) >= 2 in HAVING
> 3. Compute SUM(total_amount) for qualifying pairs

---

### Break down the requirements

#### Step 1: Group by user-product pair

`GROUP BY user_id, product_id` produces one row per pair.

#### Step 2: Apply the universal condition

`HAVING MIN(quantity) >= 2` ensures every transaction in the group has quantity >= 2.

#### Step 3: Compute and sort

`SUM(total_amount)` for revenue, ordered by user ascending and revenue descending.

---

### The solution

**Universal quantifier via MIN in HAVING**

```sql
SELECT
    user_id,
    product_id,
    SUM(total_amount) AS total_revenue
FROM transactions
GROUP BY user_id, product_id
HAVING MIN(quantity) >= 2
ORDER BY user_id ASC, total_revenue DESC
```

> **Cost Analysis**
>
> Scan of 70M rows with GROUP BY on (user_id, product_id). The number of groups depends on how many unique pairs exist. MIN and SUM are computed in the same aggregation pass.

> **Interviewers Watch For**
>
> The MIN-in-HAVING trick is the SQL equivalent of a universal quantifier. Candidates who use NOT EXISTS with a correlated subquery checking for quantity < 2 are also correct but less elegant.

> **Common Pitfall**
>
> Using `HAVING AVG(quantity) >= 2` would incorrectly include pairs where some transactions have quantity 1 and others have higher values that pull the average up.

---

## Common follow-up questions

- How would you express this with NOT EXISTS instead? _(NOT EXISTS (SELECT 1 FROM transactions t2 WHERE t2.user_id = t.user_id AND t2.product_id = t.product_id AND t2.quantity < 2). Tests correlated subquery fluency.)_
- What if the condition was 'at least 80% of transactions have quantity >= 2'? _(Tests conditional count ratio: HAVING SUM(CASE WHEN quantity >= 2 THEN 1.0 ELSE 0 END) / COUNT(*) >= 0.8.)_
- What if you needed to show which transactions disqualified a pair? _(Requires a different approach: anti-join or window function to tag violating rows.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/consistent_high_quantity_revenue)
- [SQL Interview Questions](https://datadriven.io/sql-interview-questions)
- [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep)
- [Daily Challenge](https://datadriven.io/daily)

---

Source: DataDriven (https://datadriven.io). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.