IntermediateDate & Time Functions

Find the busiest day of week for orders

The query

SQL

SELECT
  TO_CHAR(order_date, 'Day') AS day_name,
  EXTRACT(DOW FROM order_date) AS day_number,
  COUNT(*) AS order_count,
  ROUND(AVG(total), 2) AS avg_order_value,
  SUM(total) AS total_revenue
FROM orders
WHERE status = 'completed'
GROUP BY TO_CHAR(order_date, 'Day'), EXTRACT(DOW FROM order_date)
ORDER BY day_number;

Tested against PostgreSQL 16

Note

“DOW: 0=Sunday, 1=Monday, ..., 6=Saturday. GROUP BY both name and number to sort correctly.

Tables referenced

orders6 cols