BBampaa

Lessons › SQL › Go further in SQL: Business questions

SQL lesson

What % of each store's orders used a coupon?

What percentage of each store's orders used a coupon? A rate is some of the rows divided by all of them. Here's a trick interviewers like: COUNT(coupon) counts only the rows where coupon isn't empty, while COUNT(*) counts every row. Keep the rate as a decimal: 0.2, not 20.

Try this lesson free →
About 5–10 minutes · no sign-up needed

Example

A rate: some of them divided by all of them

SELECT store, 1.0 * COUNT(coupon) / COUNT(*) AS coupon_rate
FROM orders
GROUP BY store

Result:

storecoupon_rate
Abuja0.333
Accra0.5
Lagos0.2

Step by step

  1. A decimal like 0.2.
    SELECT store, 1.0 * COUNT(coupon) / COUNT(*) AS coupon_rate FROM orders GROUP BY store
  2. No join.
    SELECT store, 1.0 * COUNT(coupon) / COUNT(*) AS coupon_rate FROM orders GROUP BY store
  3. COUNT(coupon) skips the empty coupons.
    SELECT store, 1.0 * COUNT(coupon) / COUNT(*) AS coupon_rate FROM orders GROUP BY store
  4. Both counts go in one SELECT, with 1.0 for the decimals.
    SELECT store, 1.0 * COUNT(coupon) / COUNT(*) AS coupon_rate FROM orders GROUP BY store

Practice

Try each one in your head first, then open the answer.

1. What percentage of each store's orders used a coupon? Show store and coupon_rate, as a decimal.
SELECT store, 1.0 * COUNT(coupon) / COUNT(*) AS coupon_rate FROM orders GROUP BY store
2. What percentage of each store's orders were over 100? Show store and big_rate, as a decimal.
SELECT store, 1.0 * SUM(CASE WHEN revenue > 100 THEN 1 ELSE 0 END) / COUNT(*) AS big_rate FROM orders GROUP BY store
3. What percentage of each product's orders used a coupon? Show product and coupon_rate, as a decimal.
SELECT product, 1.0 * COUNT(coupon) / COUNT(*) AS coupon_rate FROM orders GROUP BY product
4. What share of each store's revenue came from coupon orders? Show store and coupon_share, as a decimal.
SELECT store, 1.0 * SUM(CASE WHEN coupon IS NOT NULL THEN revenue ELSE 0 END) / SUM(revenue) AS coupon_share FROM orders GROUP BY store
5. Which stores had a coupon on more than a third of their orders? Show store and coupon_rate.
SELECT store, 1.0 * COUNT(coupon) / COUNT(*) AS coupon_rate FROM orders GROUP BY store HAVING 1.0 * COUNT(coupon) / COUNT(*) > 1.0 / 3

Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.

Try this lesson free →
About 5–10 minutes · no sign-up needed