magento2-order-counts.sql

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
    WHERE o.created_at > date_add('2000-01-01', INTERVAL @utc_offset HOUR) AND o.created_at < date_add('2020-01-01', INTERVAL @utc_offset HOUR)
    GROUP BY date_format(date_sub(o.created_at, INTERVAL @utc_offset HOUR), "%Y")
) t GROUP BY period_date ORDER BY period_date DESC;

-- Orders Per Day for Last Month --
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-%m-%d") AS period_date
    FROM sales_order o
    WHERE o.created_at > date_add(date_sub(curdate(), INTERVAL 1 MONTH ), INTERVAL @utc_offset HOUR)
    GROUP BY date_format(date_sub(o.created_at, INTERVAL @utc_offset HOUR), "%Y-%m-%d")
) t GROUP BY period_date ORDER BY period_date DESC;

-- Orders Per Hour for Last 24 Hrs --
SELECT period_date, period, 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-%m-%d") AS period_date,
        date_format(date_sub(o.created_at, INTERVAL @utc_offset HOUR), "%H") AS period
    FROM sales_order o
    WHERE o.created_at > date_add(date_sub(curdate(), INTERVAL 1 DAY ), INTERVAL @utc_offset HOUR)
    GROUP BY date_format(date_sub(o.created_at, INTERVAL @utc_offset HOUR), "%Y-%m-%d %H")
) t GROUP BY period_date, period ORDER BY period_date DESC, period DESC LIMIT 24;

-- peak orders per hour --
SELECT period_date, period, 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-%m-%d") AS period_date,
        date_format(date_sub(o.created_at, INTERVAL @utc_offset HOUR), "%H") AS period
    FROM sales_order o
    WHERE o.created_at > date_add('2000-01-01', INTERVAL @utc_offset HOUR) AND o.created_at < date_add('2020-01-01', INTERVAL @utc_offset HOUR)
    GROUP BY date_format(date_sub(o.created_at, INTERVAL @utc_offset HOUR), "%Y-%m-%d %H")
) t GROUP BY period_date, period ORDER BY order_count DESC, period DESC LIMIT 10;

-- peak orders per day --
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-%m-%d") AS period_date
    FROM sales_order o
    WHERE o.created_at > date_add('2000-01-01', INTERVAL @utc_offset HOUR) AND o.created_at < date_add('2020-01-01', INTERVAL @utc_offset HOUR)
    GROUP BY date_format(date_sub(o.created_at, INTERVAL @utc_offset HOUR), "%Y-%m-%d")
) t GROUP BY period_date ORDER BY order_count DESC LIMIT 10;

-- peak orders per week --
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,
        CONCAT(date_format(date_sub(o.created_at, INTERVAL @utc_offset HOUR), "%Y-"), WEEK(date_sub(o.created_at, INTERVAL @utc_offset HOUR))) AS period_date
    FROM sales_order o
    WHERE o.created_at > date_add('2000-01-01', INTERVAL @utc_offset HOUR) AND o.created_at < date_add('2020-01-01', INTERVAL @utc_offset HOUR)
    GROUP BY CONCAT(date_format(date_sub(o.created_at, INTERVAL @utc_offset HOUR), "%Y-"), WEEK(date_sub(o.created_at, INTERVAL @utc_offset HOUR)))
) t GROUP BY period_date ORDER BY order_count DESC LIMIT 10;

-- avg orders per day of week --
SELECT period, CONCAT("UTC-", @utc_offset) AS utc_offset, ROUND(AVG(order_count), 1) AS avg_orders, MAX(order_count) AS max_orders, ROUND(AVG(gross_revenue), 2) AS avg_gross_revenue, MAX(gross_revenue) AS max_gross_revenue
FROM (
    SELECT
        COUNT(*) AS order_count,
        ROUND(SUM(base_grand_total), 2) AS gross_revenue,
        DAYOFWEEK(date_sub(o.created_at, INTERVAL @utc_offset HOUR)) AS period
    FROM sales_order o
    WHERE o.created_at > date_add('2016-01-01', INTERVAL @utc_offset HOUR) AND o.created_at < date_add('2020-01-01', INTERVAL @utc_offset HOUR)
    GROUP BY CONCAT(date_format(date_sub(o.created_at, INTERVAL @utc_offset HOUR), "%Y-"), WEEK(date_sub(o.created_at, INTERVAL @utc_offset HOUR)), DAYOFWEEK(date_sub(o.created_at, INTERVAL @utc_offset HOUR)))
) t GROUP BY period ORDER BY period;

-- avg and max orders per hourly period --
SELECT period, CONCAT("UTC-", @utc_offset) AS utc_offset, ROUND(AVG(num), 1) AS avg_orders, MAX(num) AS max_orders, ROUND(AVG(gross_revenue), 2) AS avg_gross_revenue, MAX(gross_revenue) AS max_gross_revenue
FROM (
    SELECT
        COUNT(*) AS num,
        ROUND(SUM(base_grand_total), 2) AS gross_revenue,
        date_format(date_sub(o.created_at, INTERVAL @utc_offset HOUR), "%H") AS period
    FROM sales_order o
    WHERE o.created_at > date_add('2016-01-01', INTERVAL @utc_offset HOUR) AND o.created_at < date_add('2020-01-01', INTERVAL @utc_offset HOUR)
    GROUP BY date_format(date_sub(o.created_at, INTERVAL @utc_offset HOUR), "%Y-%m-%d %H")
) t GROUP BY period ORDER BY period;