Calculate Days Since Order (Foundational)

basic

E-commerce dashboard shows order status: "Order placed 5 days ago". Calculate age of all recent orders.

SELECT

order_id,

customer_name,

order_date,

CURRENT_DATE - order_date AS days_since_order,

CASE

WHEN CURRENT_DATE - order_date <= 7 THEN 'Fresh'

WHEN CURRENT_DATE - order_date <= 30 THEN 'Recent'

ELSE 'Older'

END AS freshness

FROM orders

WHERE order_date >= CURRENT_DATE - INTERVAL '90 days'

ORDER BY order_date DESC;

order_id | customer_name | order_date | days_since_order | freshness

1001 | John Doe | 2024-12-20 | 5 | Fresh

1002 | Jane Smith | 2024-12-18 | 7 | Fresh

1003 | Bob Johnson | 2024-11-25 | 30 | Recent

Date subtraction returns days. CASE for bucketing ages. WHERE filters by date range (efficient). Real-world: dashboards need age calculations for order tracking.

All

Extract Year, Month, Day for Reporting

basic

Group sales by year-month for trend analysis. Show breakdown by each year and month separately.

SELECT

EXTRACT(YEAR FROM order_date) AS year,

EXTRACT(MONTH FROM order_date) AS month,

COUNT(*) AS order_count,

SUM(total_amount) AS monthly_revenue

FROM orders

WHERE order_date >= CURRENT_DATE - INTERVAL '2 years'

GROUP BY

EXTRACT(YEAR FROM order_date),

EXTRACT(MONTH FROM order_date)

ORDER BY year DESC, month DESC;

year | month | order_count | monthly_revenue

2024 | 12 | 2,450 | $125,600

2024 | 11 | 2,340 | $118,900

2024 | 10 | 2,100 | $105,200