0001 SELECT
0002 w_warehouse_name,
0003 w_warehouse_sq_ft,
0004 w_city,
0005 w_county,
0006 w_state,
0007 w_country,
0008 ship_carriers,
0009 year,
0010 sum(jan_sales) AS jan_sales,
0011 sum(feb_sales) AS feb_sales,
0012 sum(mar_sales) AS mar_sales,
0013 sum(apr_sales) AS apr_sales,
0014 sum(may_sales) AS may_sales,
0015 sum(jun_sales) AS jun_sales,
0016 sum(jul_sales) AS jul_sales,
0017 sum(aug_sales) AS aug_sales,
0018 sum(sep_sales) AS sep_sales,
0019 sum(oct_sales) AS oct_sales,
0020 sum(nov_sales) AS nov_sales,
0021 sum(dec_sales) AS dec_sales,
0022 sum(jan_sales / w_warehouse_sq_ft) AS jan_sales_per_sq_foot,
0023 sum(feb_sales / w_warehouse_sq_ft) AS feb_sales_per_sq_foot,
0024 sum(mar_sales / w_warehouse_sq_ft) AS mar_sales_per_sq_foot,
0025 sum(apr_sales / w_warehouse_sq_ft) AS apr_sales_per_sq_foot,
0026 sum(may_sales / w_warehouse_sq_ft) AS may_sales_per_sq_foot,
0027 sum(jun_sales / w_warehouse_sq_ft) AS jun_sales_per_sq_foot,
0028 sum(jul_sales / w_warehouse_sq_ft) AS jul_sales_per_sq_foot,
0029 sum(aug_sales / w_warehouse_sq_ft) AS aug_sales_per_sq_foot,
0030 sum(sep_sales / w_warehouse_sq_ft) AS sep_sales_per_sq_foot,
0031 sum(oct_sales / w_warehouse_sq_ft) AS oct_sales_per_sq_foot,
0032 sum(nov_sales / w_warehouse_sq_ft) AS nov_sales_per_sq_foot,
0033 sum(dec_sales / w_warehouse_sq_ft) AS dec_sales_per_sq_foot,
0034 sum(jan_net) AS jan_net,
0035 sum(feb_net) AS feb_net,
0036 sum(mar_net) AS mar_net,
0037 sum(apr_net) AS apr_net,
0038 sum(may_net) AS may_net,
0039 sum(jun_net) AS jun_net,
0040 sum(jul_net) AS jul_net,
0041 sum(aug_net) AS aug_net,
0042 sum(sep_net) AS sep_net,
0043 sum(oct_net) AS oct_net,
0044 sum(nov_net) AS nov_net,
0045 sum(dec_net) AS dec_net
0046 FROM (
0047 (SELECT
0048 w_warehouse_name,
0049 w_warehouse_sq_ft,
0050 w_city,
0051 w_county,
0052 w_state,
0053 w_country,
0054 concat('DHL', ',', 'BARIAN') AS ship_carriers,
0055 d_year AS year,
0056 sum(CASE WHEN d_moy = 1
0057 THEN ws_ext_sales_price * ws_quantity
0058 ELSE 0 END) AS jan_sales,
0059 sum(CASE WHEN d_moy = 2
0060 THEN ws_ext_sales_price * ws_quantity
0061 ELSE 0 END) AS feb_sales,
0062 sum(CASE WHEN d_moy = 3
0063 THEN ws_ext_sales_price * ws_quantity
0064 ELSE 0 END) AS mar_sales,
0065 sum(CASE WHEN d_moy = 4
0066 THEN ws_ext_sales_price * ws_quantity
0067 ELSE 0 END) AS apr_sales,
0068 sum(CASE WHEN d_moy = 5
0069 THEN ws_ext_sales_price * ws_quantity
0070 ELSE 0 END) AS may_sales,
0071 sum(CASE WHEN d_moy = 6
0072 THEN ws_ext_sales_price * ws_quantity
0073 ELSE 0 END) AS jun_sales,
0074 sum(CASE WHEN d_moy = 7
0075 THEN ws_ext_sales_price * ws_quantity
0076 ELSE 0 END) AS jul_sales,
0077 sum(CASE WHEN d_moy = 8
0078 THEN ws_ext_sales_price * ws_quantity
0079 ELSE 0 END) AS aug_sales,
0080 sum(CASE WHEN d_moy = 9
0081 THEN ws_ext_sales_price * ws_quantity
0082 ELSE 0 END) AS sep_sales,
0083 sum(CASE WHEN d_moy = 10
0084 THEN ws_ext_sales_price * ws_quantity
0085 ELSE 0 END) AS oct_sales,
0086 sum(CASE WHEN d_moy = 11
0087 THEN ws_ext_sales_price * ws_quantity
0088 ELSE 0 END) AS nov_sales,
0089 sum(CASE WHEN d_moy = 12
0090 THEN ws_ext_sales_price * ws_quantity
0091 ELSE 0 END) AS dec_sales,
0092 sum(CASE WHEN d_moy = 1
0093 THEN ws_net_paid * ws_quantity
0094 ELSE 0 END) AS jan_net,
0095 sum(CASE WHEN d_moy = 2
0096 THEN ws_net_paid * ws_quantity
0097 ELSE 0 END) AS feb_net,
0098 sum(CASE WHEN d_moy = 3
0099 THEN ws_net_paid * ws_quantity
0100 ELSE 0 END) AS mar_net,
0101 sum(CASE WHEN d_moy = 4
0102 THEN ws_net_paid * ws_quantity
0103 ELSE 0 END) AS apr_net,
0104 sum(CASE WHEN d_moy = 5
0105 THEN ws_net_paid * ws_quantity
0106 ELSE 0 END) AS may_net,
0107 sum(CASE WHEN d_moy = 6
0108 THEN ws_net_paid * ws_quantity
0109 ELSE 0 END) AS jun_net,
0110 sum(CASE WHEN d_moy = 7
0111 THEN ws_net_paid * ws_quantity
0112 ELSE 0 END) AS jul_net,
0113 sum(CASE WHEN d_moy = 8
0114 THEN ws_net_paid * ws_quantity
0115 ELSE 0 END) AS aug_net,
0116 sum(CASE WHEN d_moy = 9
0117 THEN ws_net_paid * ws_quantity
0118 ELSE 0 END) AS sep_net,
0119 sum(CASE WHEN d_moy = 10
0120 THEN ws_net_paid * ws_quantity
0121 ELSE 0 END) AS oct_net,
0122 sum(CASE WHEN d_moy = 11
0123 THEN ws_net_paid * ws_quantity
0124 ELSE 0 END) AS nov_net,
0125 sum(CASE WHEN d_moy = 12
0126 THEN ws_net_paid * ws_quantity
0127 ELSE 0 END) AS dec_net
0128 FROM
0129 web_sales, warehouse, date_dim, time_dim, ship_mode
0130 WHERE
0131 ws_warehouse_sk = w_warehouse_sk
0132 AND ws_sold_date_sk = d_date_sk
0133 AND ws_sold_time_sk = t_time_sk
0134 AND ws_ship_mode_sk = sm_ship_mode_sk
0135 AND d_year = 2001
0136 AND t_time BETWEEN 30838 AND 30838 + 28800
0137 AND sm_carrier IN ('DHL', 'BARIAN')
0138 GROUP BY
0139 w_warehouse_name, w_warehouse_sq_ft, w_city, w_county, w_state, w_country, d_year)
0140 UNION ALL
0141 (SELECT
0142 w_warehouse_name,
0143 w_warehouse_sq_ft,
0144 w_city,
0145 w_county,
0146 w_state,
0147 w_country,
0148 concat('DHL', ',', 'BARIAN') AS ship_carriers,
0149 d_year AS year,
0150 sum(CASE WHEN d_moy = 1
0151 THEN cs_sales_price * cs_quantity
0152 ELSE 0 END) AS jan_sales,
0153 sum(CASE WHEN d_moy = 2
0154 THEN cs_sales_price * cs_quantity
0155 ELSE 0 END) AS feb_sales,
0156 sum(CASE WHEN d_moy = 3
0157 THEN cs_sales_price * cs_quantity
0158 ELSE 0 END) AS mar_sales,
0159 sum(CASE WHEN d_moy = 4
0160 THEN cs_sales_price * cs_quantity
0161 ELSE 0 END) AS apr_sales,
0162 sum(CASE WHEN d_moy = 5
0163 THEN cs_sales_price * cs_quantity
0164 ELSE 0 END) AS may_sales,
0165 sum(CASE WHEN d_moy = 6
0166 THEN cs_sales_price * cs_quantity
0167 ELSE 0 END) AS jun_sales,
0168 sum(CASE WHEN d_moy = 7
0169 THEN cs_sales_price * cs_quantity
0170 ELSE 0 END) AS jul_sales,
0171 sum(CASE WHEN d_moy = 8
0172 THEN cs_sales_price * cs_quantity
0173 ELSE 0 END) AS aug_sales,
0174 sum(CASE WHEN d_moy = 9
0175 THEN cs_sales_price * cs_quantity
0176 ELSE 0 END) AS sep_sales,
0177 sum(CASE WHEN d_moy = 10
0178 THEN cs_sales_price * cs_quantity
0179 ELSE 0 END) AS oct_sales,
0180 sum(CASE WHEN d_moy = 11
0181 THEN cs_sales_price * cs_quantity
0182 ELSE 0 END) AS nov_sales,
0183 sum(CASE WHEN d_moy = 12
0184 THEN cs_sales_price * cs_quantity
0185 ELSE 0 END) AS dec_sales,
0186 sum(CASE WHEN d_moy = 1
0187 THEN cs_net_paid_inc_tax * cs_quantity
0188 ELSE 0 END) AS jan_net,
0189 sum(CASE WHEN d_moy = 2
0190 THEN cs_net_paid_inc_tax * cs_quantity
0191 ELSE 0 END) AS feb_net,
0192 sum(CASE WHEN d_moy = 3
0193 THEN cs_net_paid_inc_tax * cs_quantity
0194 ELSE 0 END) AS mar_net,
0195 sum(CASE WHEN d_moy = 4
0196 THEN cs_net_paid_inc_tax * cs_quantity
0197 ELSE 0 END) AS apr_net,
0198 sum(CASE WHEN d_moy = 5
0199 THEN cs_net_paid_inc_tax * cs_quantity
0200 ELSE 0 END) AS may_net,
0201 sum(CASE WHEN d_moy = 6
0202 THEN cs_net_paid_inc_tax * cs_quantity
0203 ELSE 0 END) AS jun_net,
0204 sum(CASE WHEN d_moy = 7
0205 THEN cs_net_paid_inc_tax * cs_quantity
0206 ELSE 0 END) AS jul_net,
0207 sum(CASE WHEN d_moy = 8
0208 THEN cs_net_paid_inc_tax * cs_quantity
0209 ELSE 0 END) AS aug_net,
0210 sum(CASE WHEN d_moy = 9
0211 THEN cs_net_paid_inc_tax * cs_quantity
0212 ELSE 0 END) AS sep_net,
0213 sum(CASE WHEN d_moy = 10
0214 THEN cs_net_paid_inc_tax * cs_quantity
0215 ELSE 0 END) AS oct_net,
0216 sum(CASE WHEN d_moy = 11
0217 THEN cs_net_paid_inc_tax * cs_quantity
0218 ELSE 0 END) AS nov_net,
0219 sum(CASE WHEN d_moy = 12
0220 THEN cs_net_paid_inc_tax * cs_quantity
0221 ELSE 0 END) AS dec_net
0222 FROM
0223 catalog_sales, warehouse, date_dim, time_dim, ship_mode
0224 WHERE
0225 cs_warehouse_sk = w_warehouse_sk
0226 AND cs_sold_date_sk = d_date_sk
0227 AND cs_sold_time_sk = t_time_sk
0228 AND cs_ship_mode_sk = sm_ship_mode_sk
0229 AND d_year = 2001
0230 AND t_time BETWEEN 30838 AND 30838 + 28800
0231 AND sm_carrier IN ('DHL', 'BARIAN')
0232 GROUP BY
0233 w_warehouse_name, w_warehouse_sq_ft, w_city, w_county, w_state, w_country, d_year
0234 )
0235 ) x
0236 GROUP BY
0237 w_warehouse_name, w_warehouse_sq_ft, w_city, w_county, w_state, w_country,
0238 ship_carriers, year
0239 ORDER BY w_warehouse_name
0240 LIMIT 100
