IntermediateGROUP BY & Aggregation

Daily active users (orders per day)

The query

SQL

SELECT
  order_date::DATE AS date,
  COUNT(DISTINCT cust_id) AS unique_customers,
  COUNT(*) AS total_orders,
  SUM(total) AS daily_revenue,
  ROUND(AVG(total), 2) AS avg_order_value
FROM orders
GROUP BY order_date::DATE
ORDER BY date DESC
LIMIT 30;

Tested against PostgreSQL 16

Note

“::DATE truncates timestamp to date. COUNT(DISTINCT) for unique customers, COUNT(*) for all orders.

Tables referenced

orders6 cols