datastore/tests/benchmarks/tpc-ds/queries/query_66.sql

-- Added casts like sum(x.jan_sales / w_warehouse_sq_ft) -> sum(CAST(x.jan_sales AS Float64) / w_warehouse_sq_ft) for precision reasons.
-- This is legal in TPC-DS SQL standard compliance.

SELECT
    w_warehouse_name,
    w_warehouse_sq_ft,
    w_city,
    w_county,
    w_state,
    w_country,
    ship_carriers,
    year,
    sum(x.jan_sales) AS jan_sales,
    sum(x.feb_sales) AS feb_sales,
    sum(x.mar_sales) AS mar_sales,
    sum(x.apr_sales) AS apr_sales,
    sum(x.may_sales) AS may_sales,
    sum(x.jun_sales) AS jun_sales,
    sum(x.jul_sales) AS jul_sales,
    sum(x.aug_sales) AS aug_sales,
    sum(x.sep_sales) AS sep_sales,
    sum(x.oct_sales) AS oct_sales,
    sum(x.nov_sales) AS nov_sales,
    sum(x.dec_sales) AS dec_sales,
    sum(CAST(x.jan_sales AS Float64) / w_warehouse_sq_ft) AS jan_sales_per_sq_foot,
    sum(CAST(x.feb_sales AS Float64) / w_warehouse_sq_ft) AS feb_sales_per_sq_foot,
    sum(CAST(x.mar_sales AS Float64) / w_warehouse_sq_ft) AS mar_sales_per_sq_foot,
    sum(CAST(x.apr_sales AS Float64) / w_warehouse_sq_ft) AS apr_sales_per_sq_foot,
    sum(CAST(x.may_sales AS Float64) / w_warehouse_sq_ft) AS may_sales_per_sq_foot,
    sum(CAST(x.jun_sales AS Float64) / w_warehouse_sq_ft) AS jun_sales_per_sq_foot,
    sum(CAST(x.jul_sales AS Float64) / w_warehouse_sq_ft) AS jul_sales_per_sq_foot,
    sum(CAST(x.aug_sales AS Float64) / w_warehouse_sq_ft) AS aug_sales_per_sq_foot,
    sum(CAST(x.sep_sales AS Float64) / w_warehouse_sq_ft) AS sep_sales_per_sq_foot,
    sum(CAST(x.oct_sales AS Float64) / w_warehouse_sq_ft) AS oct_sales_per_sq_foot,
    sum(CAST(x.nov_sales AS Float64) / w_warehouse_sq_ft) AS nov_sales_per_sq_foot,
    sum(CAST(x.dec_sales AS Float64) / w_warehouse_sq_ft) AS dec_sales_per_sq_foot,
    sum(x.jan_net) AS jan_net,
    sum(x.feb_net) AS feb_net,
    sum(x.mar_net) AS mar_net,
    sum(x.apr_net) AS apr_net,
    sum(x.may_net) AS may_net,
    sum(x.jun_net) AS jun_net,
    sum(x.jul_net) AS jul_net,
    sum(x.aug_net) AS aug_net,
    sum(x.sep_net) AS sep_net,
    sum(x.oct_net) AS oct_net,
    sum(x.nov_net) AS nov_net,
    sum(x.dec_net) AS dec_net
FROM
(
    SELECT
        w_warehouse_name,
        w_warehouse_sq_ft,
        w_city,
        w_county,
        w_state,
        w_country,
        concat('DHL', ',', 'BARIAN') AS ship_carriers,
        d_year AS year,
        sum(multiIf(d_moy = 1, ws_ext_sales_price * ws_quantity, 0)) AS jan_sales,
        sum(multiIf(d_moy = 2, ws_ext_sales_price * ws_quantity, 0)) AS feb_sales,
        sum(multiIf(d_moy = 3, ws_ext_sales_price * ws_quantity, 0)) AS mar_sales,
        sum(multiIf(d_moy = 4, ws_ext_sales_price * ws_quantity, 0)) AS apr_sales,
        sum(multiIf(d_moy = 5, ws_ext_sales_price * ws_quantity, 0)) AS may_sales,
        sum(multiIf(d_moy = 6, ws_ext_sales_price * ws_quantity, 0)) AS jun_sales,
        sum(multiIf(d_moy = 7, ws_ext_sales_price * ws_quantity, 0)) AS jul_sales,
        sum(multiIf(d_moy = 8, ws_ext_sales_price * ws_quantity, 0)) AS aug_sales,
        sum(multiIf(d_moy = 9, ws_ext_sales_price * ws_quantity, 0)) AS sep_sales,
        sum(multiIf(d_moy = 10, ws_ext_sales_price * ws_quantity, 0)) AS oct_sales,
        sum(multiIf(d_moy = 11, ws_ext_sales_price * ws_quantity, 0)) AS nov_sales,
        sum(multiIf(d_moy = 12, ws_ext_sales_price * ws_quantity, 0)) AS dec_sales,
        sum(multiIf(d_moy = 1, ws_net_paid * ws_quantity, 0)) AS jan_net,
        sum(multiIf(d_moy = 2, ws_net_paid * ws_quantity, 0)) AS feb_net,
        sum(multiIf(d_moy = 3, ws_net_paid * ws_quantity, 0)) AS mar_net,
        sum(multiIf(d_moy = 4, ws_net_paid * ws_quantity, 0)) AS apr_net,
        sum(multiIf(d_moy = 5, ws_net_paid * ws_quantity, 0)) AS may_net,
        sum(multiIf(d_moy = 6, ws_net_paid * ws_quantity, 0)) AS jun_net,
        sum(multiIf(d_moy = 7, ws_net_paid * ws_quantity, 0)) AS jul_net,
        sum(multiIf(d_moy = 8, ws_net_paid * ws_quantity, 0)) AS aug_net,
        sum(multiIf(d_moy = 9, ws_net_paid * ws_quantity, 0)) AS sep_net,
        sum(multiIf(d_moy = 10, ws_net_paid * ws_quantity, 0)) AS oct_net,
        sum(multiIf(d_moy = 11, ws_net_paid * ws_quantity, 0)) AS nov_net,
        sum(multiIf(d_moy = 12, ws_net_paid * ws_quantity, 0)) AS dec_net
    FROM web_sales, warehouse, date_dim, time_dim, ship_mode
    WHERE (ws_warehouse_sk = w_warehouse_sk) AND (ws_sold_date_sk = d_date_sk) AND (ws_sold_time_sk = t_time_sk) AND (ws_ship_mode_sk = sm_ship_mode_sk) AND (d_year = 2001) AND (t_time BETWEEN 30838 AND 30838 + 28800) AND (sm_carrier IN ('DHL', 'BARIAN'))
    GROUP BY
        w_warehouse_name,
        w_warehouse_sq_ft,
        w_city,
        w_county,
        w_state,
        w_country,
        d_year
    UNION ALL
    SELECT
        w_warehouse_name,
        w_warehouse_sq_ft,
        w_city,
        w_county,
        w_state,
        w_country,
        concat('DHL', ',', 'BARIAN') AS ship_carriers,
        d_year AS year,
        sum(multiIf(d_moy = 1, cs_sales_price * cs_quantity, 0)) AS jan_sales,
        sum(multiIf(d_moy = 2, cs_sales_price * cs_quantity, 0)) AS feb_sales,
        sum(multiIf(d_moy = 3, cs_sales_price * cs_quantity, 0)) AS mar_sales,
        sum(multiIf(d_moy = 4, cs_sales_price * cs_quantity, 0)) AS apr_sales,
        sum(multiIf(d_moy = 5, cs_sales_price * cs_quantity, 0)) AS may_sales,
        sum(multiIf(d_moy = 6, cs_sales_price * cs_quantity, 0)) AS jun_sales,
        sum(multiIf(d_moy = 7, cs_sales_price * cs_quantity, 0)) AS jul_sales,
        sum(multiIf(d_moy = 8, cs_sales_price * cs_quantity, 0)) AS aug_sales,
        sum(multiIf(d_moy = 9, cs_sales_price * cs_quantity, 0)) AS sep_sales,
        sum(multiIf(d_moy = 10, cs_sales_price * cs_quantity, 0)) AS oct_sales,
        sum(multiIf(d_moy = 11, cs_sales_price * cs_quantity, 0)) AS nov_sales,
        sum(multiIf(d_moy = 12, cs_sales_price * cs_quantity, 0)) AS dec_sales,
        sum(multiIf(d_moy = 1, cs_net_paid_inc_tax * cs_quantity, 0)) AS jan_net,
        sum(multiIf(d_moy = 2, cs_net_paid_inc_tax * cs_quantity, 0)) AS feb_net,
        sum(multiIf(d_moy = 3, cs_net_paid_inc_tax * cs_quantity, 0)) AS mar_net,
        sum(multiIf(d_moy = 4, cs_net_paid_inc_tax * cs_quantity, 0)) AS apr_net,
        sum(multiIf(d_moy = 5, cs_net_paid_inc_tax * cs_quantity, 0)) AS may_net,
        sum(multiIf(d_moy = 6, cs_net_paid_inc_tax * cs_quantity, 0)) AS jun_net,
        sum(multiIf(d_moy = 7, cs_net_paid_inc_tax * cs_quantity, 0)) AS jul_net,
        sum(multiIf(d_moy = 8, cs_net_paid_inc_tax * cs_quantity, 0)) AS aug_net,
        sum(multiIf(d_moy = 9, cs_net_paid_inc_tax * cs_quantity, 0)) AS sep_net,
        sum(multiIf(d_moy = 10, cs_net_paid_inc_tax * cs_quantity, 0)) AS oct_net,
        sum(multiIf(d_moy = 11, cs_net_paid_inc_tax * cs_quantity, 0)) AS nov_net,
        sum(multiIf(d_moy = 12, cs_net_paid_inc_tax * cs_quantity, 0)) AS dec_net
    FROM catalog_sales, warehouse, date_dim, time_dim, ship_mode
    WHERE (cs_warehouse_sk = w_warehouse_sk) AND (cs_sold_date_sk = d_date_sk) AND (cs_sold_time_sk = t_time_sk) AND (cs_ship_mode_sk = sm_ship_mode_sk) AND (d_year = 2001) AND ((t_time >= 30838) AND (t_time <= (30838 + 28800))) AND (sm_carrier IN ('DHL', 'BARIAN'))
    GROUP BY
        w_warehouse_name,
        w_warehouse_sq_ft,
        w_city,
        w_county,
        w_state,
        w_country,
        d_year
) AS x
GROUP BY
    w_warehouse_name,
    w_warehouse_sq_ft,
    w_city,
    w_county,
    w_state,
    w_country,
    ship_carriers,
    year
ORDER BY w_warehouse_name
LIMIT 100;