x

WITH t2 AS
              (SELECT `ca_address_sk`,
                      `ca_state` IN ('CO',
                                     'IL',
                                     'MN') AS `IN`,
                                    `ca_state` IN ('OH',
                                                   'MT',
                                                   'NM') AS `IN2`,
                                                  `ca_state` IN ('TX',
                                                                 'MO',
                                                                 'MI') AS `IN3`
               FROM `tpcds_1000_parquet`.`customer_address`
               WHERE `ca_state` IN ('CO',
                                    'IL',
                                    'MN',
                                    'OH',
                                    'MT',
                                    'NM',
                                    'TX',
                                    'MO',
                                    'MI')
               AND `ca_country` = 'United States'),
t0 as
  (SELECT `cd_demo_sk`,
          `cd_marital_status` = 'D' AS `=`,
          `cd_education_status` = '2 yr Degree' AS `=2`,
          `cd_marital_status` = 'S' AS `=3`,
          `cd_education_status` = 'Secondary' AS `=4`,
          `cd_marital_status` = 'W' AS `=5`,
          `cd_education_status` = 'Advanced Degree' AS `=6`
   FROM `tpcds_1000_parquet`.`customer_demographics`
   WHERE `cd_marital_status` IN ('D',
                                 'S',
                                 'W')
     AND `cd_education_status` IN ('2 yr Degree',
                                   'Secondary',
                                   'Advanced Degree')),
t4 as
                          (SELECT `hd_demo_sk`,
                                  `hd_dep_count` = 3 AS `=`,
                                  `hd_dep_count` = 1 AS `=2`
                           FROM `tpcds_1000_parquet`.`household_demographics`
                           WHERE `hd_dep_count` IN (3,
                                                    1)),

t6 as                                       (SELECT `d_date_sk`
                                       FROM `tpcds_1000_parquet`.`date_dim`
                                       WHERE `d_year` = 2001),
t8 as
                                      (SELECT `ss_cdemo_sk`,
                                              `ss_hdemo_sk`,
                                              `ss_addr_sk`,
                                              `ss_quantity`,
                                              `ss_ext_sales_price`,
                                              `ss_ext_wholesale_cost`,
                                              `ss_sold_date_sk`,
                                              `ss_net_profit` BETWEEN 100 AND 200 AS `BETWEEN`,
                                                                              `ss_net_profit` BETWEEN 150 AND 300 AS `BETWEEN9`,
                                                                                                              `ss_net_profit` BETWEEN 50 AND 250 AS `BETWEEN10`,
                                                                                                                                             `ss_sales_price` BETWEEN 100 AND 150 AS `BETWEEN11`,
                                                                                                                                                                              `ss_sales_price` BETWEEN 50 AND 100 AS `BETWEEN12`,
                                                                                                                                                                                                              `ss_sales_price` BETWEEN 150 AND 200 AS `BETWEEN13`
                                       FROM `tpcds_1000_parquet`.`store_sales`
                                       WHERE (`ss_sales_price` BETWEEN 100 AND 150
                                              OR `ss_sales_price` BETWEEN 50 AND 100
                                              OR `ss_sales_price` BETWEEN 150 AND 200)
                                         AND (`ss_net_profit` BETWEEN 100 AND 200
                                              OR `ss_net_profit` BETWEEN 150 AND 300
                                              OR `ss_net_profit` BETWEEN 50 AND 250)
                                         AND `ss_store_sk` IS NOT NULL
                                         AND `ss_cdemo_sk` IS NOT NULL
                                         AND `ss_hdemo_sk` IS NOT NULL
                                         AND `ss_addr_sk` IS NOT NULL
                                         AND `ss_sold_date_sk` IS NOT NULL)


SELECT CAST(SUM(`t8`.`ss_quantity`) AS DOUBLE) / COUNT(`t8`.`ss_quantity`) AS `$f0`,
       SUM(`t8`.`ss_ext_sales_price`) / COUNT(`t8`.`ss_ext_sales_price`) AS `$f1`,
       SUM(`t8`.`ss_ext_wholesale_cost`) / COUNT(`t8`.`ss_ext_wholesale_cost`) AS `$f2`,
       SUM(`t8`.`ss_ext_wholesale_cost`) AS `$f3`
FROM t0, t2, t4, t6, t8
WHERE `t6`.`d_date_sk` = `t8`.`ss_sold_date_sk` AND
`t4`.`hd_demo_sk` = `t8`.`ss_hdemo_sk` AND
`t2`.`ca_address_sk` = `t8`.`ss_addr_sk`
            AND (`t2`.`IN`
                 AND `t8`.`BETWEEN`
                 OR `t2`.`IN2`
                 AND `t8`.`BETWEEN9`
                 OR `t2`.`IN3`
                 AND `t8`.`BETWEEN10`)
AND `t0`.`cd_demo_sk` = `t8`.`ss_cdemo_sk`
AND (`t0`.`=`
     AND `t0`.`=2`
     AND `t8`.`BETWEEN11`
     AND `t4`.`=`
     OR `t0`.`=3`
     AND `t0`.`=4`
     AND `t8`.`BETWEEN12`
     AND `t4`.`=2`
     OR `t0`.`=5`
     AND `t0`.`=6`
     AND `t8`.`BETWEEN13`
     AND `t4`.`=2`)