Answering the Customer's Question in SQL

Goal

On top of someone else's schema, you become able to turn the customer's questions into queries and avoid the traps that are wrong without an error.

Why it matters

A query with wrong syntax tells you right away, but what is dangerous is a query that runs. If a number you read out in front of the customer was wrong, every number you say after that in the project comes under suspicion.

This lab makes you run into two of them yourself. The difference between COUNT(*) and COUNT(열) (the placeholder is the column) — the latter counts only the rows where that column is not NULL. And rows that do not exist are not joined — a customer who has never had a ticket does not appear in the INNER JOIN result at all, so to count such customers you have to find NULL after a LEFT JOIN or use NOT EXISTS.

One more thing. If there is even one NULL in a NOT IN subquery, the entire result becomes 0 rows, with no error and no warning. A business-plausible conclusion goes into the report as it is. It is safer to use NOT EXISTS as a habit.

Schema

• customers(id, name, plan, signed_at) — plan is free / pro / enterprise
• orders(id, customer_id, amount, status, created_at) — status is paid / pending / refunded
• tickets(id, customer_id, severity, opened_at, closed_at) — for a ticket that is not closed, closed_at is NULL

Steps

1. Create /root/sql and load /opt/data/support.sql into /root/sql/support.db.
2. Write the total number of customers in /root/sql/q1.txt.
3. Write the sum of the amounts of orders whose status is paid in /root/sql/q2.txt.
4. Write the name of the customer with the largest confirmed revenue in /root/sql/q3.txt.
5. Write the number of tickets that are not yet closed in /root/sql/q4.txt.
6. Write the value of SELECT COUNT(closed_at) FROM tickets; in /root/sql/q5.txt.
7. Write the number of customers who have never opened a ticket in /root/sql/q6.txt.
8. Write the plan name with the largest total confirmed revenue in /root/sql/q7.txt.

Notes

• Load: sqlite3 /root/sql/support.db < /opt/data/support.sql
• Query: sqlite3 /root/sql/support.db "SELECT COUNT(*) FROM customers;"
• Common mistake 1: in step 5, comparing with closed_at = ''. NULL is never true in any comparison, so you must use IS NULL.
• Common mistake 2: in step 8, doubting the query because the result differs from your intuition. The plan tier and the revenue size are different matters, and explaining this fact to the customer is itself a valuable discovery.

Load the snapshot

Create /root/sql and load /opt/data/support.sql into /root/sql/support.db.

sqlite3 takes SQL on standard input. Load /opt/data/support.sql into /root/sql/support.db.

Count all the customers

Write the total number of customers in /root/sql/q1.txt.

Start with the simplest question. This number becomes the denominator of every ratio afterward.

Compute the confirmed revenue total

Write the sum of the amounts of orders whose status is paid in /root/sql/q2.txt.

Not every order is revenue. First look at what values the status column has.

Find the top-revenue customer

Write the name of the customer with the largest confirmed revenue in /root/sql/q3.txt.

You need the customer name, so you need a join. Group by confirmed revenue and sort.

Count the unresolved tickets

Write the number of tickets that are not yet closed in /root/sql/q4.txt.

For a ticket that is not closed, closed_at is NULL. An empty string and NULL are different, so you must use IS NULL.

Get the value of COUNT(closed_at)

Write the value of SELECT COUNT(closed_at) FROM tickets; in /root/sql/q5.txt.

COUNT(column) counts only the rows where that column is not NULL. Think about why this value differs when the total is 80.

Count the customers with no tickets

Write the number of customers who have never opened a ticket in /root/sql/q6.txt.

Rows that do not exist are not joined. Count the ones where the right side is NULL after a LEFT JOIN, or use NOT EXISTS.

Find the top plan by revenue

Write the plan name with the largest total confirmed revenue in /root/sql/q7.txt.

Group by plan and add up the confirmed revenue. The result may differ from what you expect, and that is the point of this step.