// case studies · real world impact

Real World SQL

Explore how companies solve business‑critical problems with SQL – full schema, solution, and insight.

// SELECT A CASE STUDY

Customer Purchase Summary: Total Spent and Order Count

🏢 ShopifyBeginner

Calculate total amount spent and number of orders per customer to understand customer value.

Business Problem

A Shopify merchant success manager is building a quarterly business review and needs a straightforward customer value summary: for every customer, how many orders have they placed, how much have they spent in total, and what is their average order value? The output feeds directly into a dashboard that highlights top-spending customers for loyalty perks and flags low-value, one-time buyers for a targeted discount campaign, so the numbers need to be accurate down to the customer_id level, including customers who never converted.

Dataset & Schema

Customer data with customer_id, customer_name, city; orders with order_id, customer_id, order_amount, order_date.

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  customer_name TEXT,
  city TEXT
);
CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  order_amount DECIMAL(10,2),
  order_date DATE,
  FOREIGN KEY(customer_id) REFERENCES customers(customer_id)
);

SQL Solution

SELECT 
  c.customer_id,
  c.customer_name,
  c.city,
  COUNT(o.order_id) as total_orders,
  ROUND(SUM(o.order_amount), 2) as total_spent,
  ROUND(AVG(o.order_amount), 2) as average_order_value
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name, c.city
ORDER BY total_spent DESC;

Explanation

Step-by-step Solution:

Start with SELECT to choose columns: customer_id, customer_name, city from the customers table.

Use COUNT(o.order_id) to count the number of orders per customer.

Use SUM(o.order_amount) to calculate total amount spent. AVG(o.order_amount) shows average per order.

Use LEFT JOIN to connect customers with their orders. LEFT JOIN ensures all customers appear even if they have no orders (count = 0).

Use GROUP BY with customer_id, customer_name, city to aggregate metrics per customer.

ORDER BY total_spent DESC to show highest-value customers first. This simple query reveals your power users and helps identify which customers need attention.