SQL explainer
SELECT c.name, COUNT(o.id) AS order_count, SUM(o.total) AS lifetime_value FROM customers AS c LEFT JOIN orders AS o ON o.customer_id = c.id WHERE c.created_at >= DATE_TRUNC('year', CURRENT_DATE) GROUP BY c.id, c.name HAVING SUM(o.total) > 1000 ORDER BY lifetime_value DESC;
●Reviewing your schemadone380ms

📝 Summary

This reports customers who signed up this year and have spent more than $1,000, ranked by lifetime value.

🔎 Step by step

sql
SELECT
  c.name,
  COUNT(o.id) AS order_count,
  SUM(o.total) AS lifetime_value
FROM customers AS c
-- Keep customers even when they have no orders
LEFT JOIN orders AS o
  ON o.customer_id = c.id
-- Only customers created since January 1 of the current year
WHERE c.created_at >= DATE_TRUNC('year', CURRENT_DATE)
-- Collapse each customer's orders into one row
GROUP BY c.id, c.name
-- Filter after aggregation: keep customers above $1,000
HAVING SUM(o.total) > 1000
ORDER BY lifetime_value DESC;

📋 Returned rows

ColumnTypeDescription
nametextCustomer name
order_countbigintNumber of orders placed
lifetime_valuenumericSum of order totals

⚠️ Things to watch

  • SUM(o.total) is NULL for customers without orders, so they fail the HAVING check. Use COALESCE(SUM(o.total), 0) if they should appear with a zero total.
  • The WHERE clause filters customers, not orders. If you meant orders placed this year, move the date filter into the join condition.
  • GROUP BY c.id, c.name assumes c.id is the primary key. On databases without functional-dependency detection, include every selected column.

12s

Example conversation

Sign in to run this generator on your own schema.

Sign in to try it