davidalger

SET @utc_offset = 6;

-- Orders Per Year --
SELECT period_date, CONCAT("UTC-", @utc_offset) AS utc_offset, order_count, gross_revenue, ROUND(gross_revenue / order_count, 2) AS gross_aov
FROM (
    SELECT
        COUNT(*) AS order_count,
        ROUND(SUM(base_grand_total), 2) AS gross_revenue,
        date_format(date_sub(o.created_at, INTERVAL @utc_offset HOUR), "%Y") AS period_date
    FROM sales_order o