Grouping Data by Week and Year in PostgreSQL

SELECT
    DATE_TRUNC('week', created_at) AS sales_week,
    COUNT(id) AS total_orders,
    SUM(amount) AS total_sales
FROM
    orders
WHERE
    created_at >= NOW() - INTERVAL '1 year'
GROUP BY
    sales_week
ORDER BY
    sales_week;

How it works: This query uses PostgreSQL's `DATE_TRUNC` function to group orders by the start of each week. `DATE_TRUNC('week', ...)` truncates a timestamp to the beginning of its week, making it ideal for weekly aggregations. The `WHERE` clause filters records from the last year, and the results are ordered chronologically by week.