select
  c_last_name,
  c_first_name,
  substr(s_city, 1, 30),
  ss_ticket_number,
  amt,
  profit
from
  (select
    ss_ticket_number,
    ss_customer_sk,
    store.s_city,
    sum(ss_coupon_amt) amt,
    sum(ss_net_profit) profit
  from
    store_sales,
    date_dim,
    store,
    household_demographics
  where
    store_sales.ss_sold_date_sk = date_dim.d_date_sk
    and store_sales.ss_store_sk = store.s_store_sk
    and store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk
    and (household_demographics.hd_dep_count = 8
      or household_demographics.hd_vehicle_count > 0)
    -- and date_dim.d_dow = 1
    -- and date_dim.d_year in (1998, 1998 + 1, 1998 + 2)
    and store.s_number_employees between 200 and 295
    -- partition key filter
    -- and ss_sold_date_sk in (2450819, 2450826, 2450833, 2450840, 2450847, 2450854, 2450861, 2450868, 2450875, 2450882, 2450889,
    -- 2450896, 2450903, 2450910, 2450917, 2450924, 2450931, 2450938, 2450945, 2450952, 2450959, 2450966, 2450973, 2450980, 2450987,
    -- 2450994, 2451001, 2451008, 2451015, 2451022, 2451029, 2451036, 2451043, 2451050, 2451057, 2451064, 2451071, 2451078, 2451085,
    -- 2451092, 2451099, 2451106, 2451113, 2451120, 2451127, 2451134, 2451141, 2451148, 2451155, 2451162, 2451169, 2451176, 2451183,
    -- 2451190, 2451197, 2451204, 2451211, 2451218, 2451225, 2451232, 2451239, 2451246, 2451253, 2451260, 2451267, 2451274, 2451281,
    -- 2451288, 2451295, 2451302, 2451309, 2451316, 2451323, 2451330, 2451337, 2451344, 2451351, 2451358, 2451365, 2451372, 2451379,
    -- 2451386, 2451393, 2451400, 2451407, 2451414, 2451421, 2451428, 2451435, 2451442, 2451449, 2451456, 2451463, 2451470, 2451477,
    -- 2451484, 2451491, 2451498, 2451505, 2451512, 2451519, 2451526, 2451533, 2451540, 2451547, 2451554, 2451561, 2451568, 2451575,
    -- 2451582, 2451589, 2451596, 2451603, 2451610, 2451617, 2451624, 2451631, 2451638, 2451645, 2451652, 2451659, 2451666, 2451673,
    -- 2451680, 2451687, 2451694, 2451701, 2451708, 2451715, 2451722, 2451729, 2451736, 2451743, 2451750, 2451757, 2451764, 2451771,
    -- 2451778, 2451785, 2451792, 2451799, 2451806, 2451813, 2451820, 2451827, 2451834, 2451841, 2451848, 2451855, 2451862, 2451869,
    -- 2451876, 2451883, 2451890, 2451897, 2451904)
    and d_date between '1999-01-01' and '1999-03-31'
    and ss_sold_date_sk between 2451180 and 2451269  -- partition key filter
  group by
    ss_ticket_number,
    ss_customer_sk,
    ss_addr_sk,
    store.s_city
  ) ms,
  customer
where
  ss_customer_sk = c_customer_sk
order by
  c_last_name,
  c_first_name,
  substr(s_city, 1, 30),
  profit
limit 100;

(empty) (empty) Fairview  56491 11056.63 -25847.34		(empty) (empty) Fairview  56491 11056.63 -25847.34
(empty) (empty) Fairview 716745 11297.47 -24702.46		(empty) (empty) Fairview 716745 11297.47 -24702.46
(empty) (empty) Fairview 454139  8199.29 -23986.60		(empty) (empty) Fairview 454139  8199.29 -23986.60
(empty) (empty) Fairview 137664  4954.44 -22221.02		(empty) (empty) Fairview 137664  4954.44 -22221.02
(empty) (empty) Fairview 162777  3604.16 -21922.69		(empty) (empty) Fairview 162777  3604.16 -21922.69
(empty) (empty) Fairview 490602  2961.84 -20601.33		(empty) (empty) Fairview 490602  2961.84 -20601.33
(empty) (empty) Fairview 217173  1501.22 -18755.89		(empty) (empty) Fairview 217173  1501.22 -18755.89
(empty) (empty) Fairview 592560  6885.76 -18578.85		(empty) (empty) Fairview 592560  6885.76 -18578.85
(empty) (empty) Fairview 537685  3599.43 -17986.38		(empty) (empty) Fairview 537685  3599.43 -17986.38
(empty) (empty) Fairview 225673 15510.64 -17701.10	      <
(empty) (empty) Fairview 312575 11789.11 -17522.30		(empty) (empty) Fairview 312575 11789.11 -17522.30
(empty) (empty) Fairview 491183    23.25 -17138.08		(empty) (empty) Fairview 491183    23.25 -17138.08
(empty) (empty) Fairview 681725  1782.67 -16567.48		(empty) (empty) Fairview 681725  1782.67 -16567.48
(empty) (empty) Fairview 551114   129.92 -16221.51		(empty) (empty) Fairview 551114   129.92 -16221.51
(empty) (empty) Fairview 445950   709.98 -14568.21		(empty) (empty) Fairview 445950   709.98 -14568.21
(empty) (empty) Fairview  46677  6605.36 -14438.95		(empty) (empty) Fairview  46677  6605.36 -14438.95
(empty) (empty) Fairview  25964  1915.55 -14310.72		(empty) (empty) Fairview  25964  1915.55 -14310.72
(empty) (empty) Fairview 178164  1075.60 -14247.41		(empty) (empty) Fairview 178164  1075.60 -14247.41
(empty) (empty) Fairview 658035  6315.87 -13956.55		(empty) (empty) Fairview 658035  6315.87 -13956.55
(empty) (empty) Fairview 326093 10978.12 -13892.00		(empty) (empty) Fairview 326093 10978.12 -13892.00
(empty) (empty) Fairview 523914  2671.01 -13882.34		(empty) (empty) Fairview 523914  2671.01 -13882.34
(empty) (empty) Fairview 244624  1357.29 -13851.43		(empty) (empty) Fairview 244624  1357.29 -13851.43
(empty) (empty) Fairview  61856     0.00 -13507.59		(empty) (empty) Fairview  61856     0.00 -13507.59
(empty) (empty) Fairview 261266  5999.36 -12910.07		(empty) (empty) Fairview 261266  5999.36 -12910.07
(empty) (empty) Fairview 680049  3913.58 -12491.77		(empty) (empty) Fairview 680049  3913.58 -12491.77
(empty) (empty) Fairview 370159     0.00 -12112.66		(empty) (empty) Fairview 370159     0.00 -12112.66
(empty) (empty) Fairview 266392  1106.00 -11823.27		(empty) (empty) Fairview 266392  1106.00 -11823.27
(empty) (empty) Fairview 346539   431.27 -11373.05		(empty) (empty) Fairview 346539   431.27 -11373.05
(empty) (empty) Fairview 718294   680.84 -11331.87		(empty) (empty) Fairview 718294   680.84 -11331.87
(empty) (empty) Fairview 668190  8002.56 -10903.69		(empty) (empty) Fairview 668190  8002.56 -10903.69
(empty) (empty) Fairview 245053  4280.72 -10790.03		(empty) (empty) Fairview 245053  4280.72 -10790.03
(empty) (empty) Fairview 505857   140.79 -10763.05		(empty) (empty) Fairview 505857   140.79 -10763.05
(empty) (empty) Fairview  59078 10796.06 -10607.95		(empty) (empty) Fairview  59078 10796.06 -10607.95
(empty) (empty) Fairview 414620  7999.61 -10488.21		(empty) (empty) Fairview 414620  7999.61 -10488.21
(empty) (empty) Fairview 420379    51.67 -10463.74		(empty) (empty) Fairview 420379    51.67 -10463.74
(empty) (empty) Fairview  21512  4904.04 -10358.10		(empty) (empty) Fairview  21512  4904.04 -10358.10
(empty) (empty) Fairview 195284    30.24 -10219.51		(empty) (empty) Fairview 195284    30.24 -10219.51
(empty) (empty) Fairview 240319  8445.99 -10183.47		(empty) (empty) Fairview 240319  8445.99 -10183.47
(empty) (empty) Fairview  82206  1896.69 -10029.41		(empty) (empty) Fairview  82206  1896.69 -10029.41
(empty) (empty) Fairview 596026  2532.28 -10024.79		(empty) (empty) Fairview 596026  2532.28 -10024.79
(empty) (empty) Fairview 271856    55.89 -10017.41	      <
(empty) (empty) Fairview 283908  4434.10  -9720.46		(empty) (empty) Fairview 283908  4434.10  -9720.46
(empty) (empty) Fairview 555762    22.71  -9291.24		(empty) (empty) Fairview 555762    22.71  -9291.24
(empty) (empty) Fairview 482959   174.21  -9167.62		(empty) (empty) Fairview 482959   174.21  -9167.62
(empty) (empty) Fairview 310872  1567.12  -8669.36		(empty) (empty) Fairview 310872  1567.12  -8669.36
(empty) (empty) Fairview 620763  2777.22  -8661.75		(empty) (empty) Fairview 620763  2777.22  -8661.75
(empty) (empty) Fairview 412376  3694.69  -8646.44		(empty) (empty) Fairview 412376  3694.69  -8646.44
(empty) (empty) Fairview 136011   841.09  -8450.30		(empty) (empty) Fairview 136011   841.09  -8450.30
(empty) (empty) Fairview 213754     0.00  -8280.73		(empty) (empty) Fairview 213754     0.00  -8280.73
(empty) (empty) Fairview  48591  2054.28  -7994.84		(empty) (empty) Fairview  48591  2054.28  -7994.84
(empty) (empty) Fairview   5319     0.00  -7982.26		(empty) (empty) Fairview   5319     0.00  -7982.26
(empty) (empty) Fairview 621045  1410.12  -7740.88		(empty) (empty) Fairview 621045  1410.12  -7740.88
(empty) (empty) Fairview 184229  2485.60  -7673.40		(empty) (empty) Fairview 184229  2485.60  -7673.40
(empty) (empty) Fairview 523678  3091.14  -7120.29		(empty) (empty) Fairview 523678  3091.14  -7120.29
(empty) (empty) Fairview 374023  3595.47  -6692.39		(empty) (empty) Fairview 374023  3595.47  -6692.39
(empty) (empty) Fairview 129968  1980.45  -6581.37		(empty) (empty) Fairview 129968  1980.45  -6581.37
(empty) (empty) Fairview 468962  2548.40  -6559.98		(empty) (empty) Fairview 468962  2548.40  -6559.98
(empty) (empty) Fairview 119935   151.53  -6557.33		(empty) (empty) Fairview 119935   151.53  -6557.33
(empty) (empty) Fairview 462649  1883.62  -6358.12		(empty) (empty) Fairview 462649  1883.62  -6358.12
(empty) (empty) Fairview 579741  3640.56  -5909.47		(empty) (empty) Fairview 579741  3640.56  -5909.47
(empty) (empty) Fairview 626849  1102.12  -5802.49	      <
(empty) (empty) Fairview 573300   776.32  -5719.02		(empty) (empty) Fairview 573300   776.32  -5719.02
(empty) (empty) Fairview 719546     0.00  -5589.89		(empty) (empty) Fairview 719546     0.00  -5589.89
(empty) (empty) Fairview 323297    50.46  -5582.08		(empty) (empty) Fairview 323297    50.46  -5582.08
(empty) (empty) Fairview 375025   454.48  -5450.97		(empty) (empty) Fairview 375025   454.48  -5450.97
(empty) (empty) Fairview 411586     0.00  -5371.29		(empty) (empty) Fairview 411586     0.00  -5371.29
(empty) (empty) Fairview 392626  3852.11  -4800.58		(empty) (empty) Fairview 392626  3852.11  -4800.58
(empty) (empty) Fairview  86383   542.97  -4635.44		(empty) (empty) Fairview  86383   542.97  -4635.44
(empty) (empty) Fairview 386909     0.00  -4447.75		(empty) (empty) Fairview 386909     0.00  -4447.75
(empty) (empty) Fairview 339495   375.20  -4243.70		(empty) (empty) Fairview 339495   375.20  -4243.70
(empty) (empty) Fairview 304028     0.00  -4193.56		(empty) (empty) Fairview 304028     0.00  -4193.56
(empty) (empty) Fairview 254942     0.00  -3883.63		(empty) (empty) Fairview 254942     0.00  -3883.63
(empty) (empty) Fairview 561925  2013.84  -3610.74		(empty) (empty) Fairview 561925  2013.84  -3610.74
(empty) (empty) Fairview 193173     0.00  -3155.84		(empty) (empty) Fairview 193173     0.00  -3155.84
(empty) (empty) Fairview 562204   629.23  -2226.45		(empty) (empty) Fairview 562204   629.23  -2226.45
(empty) (empty) Fairview 648468   157.69  -2209.56		(empty) (empty) Fairview 648468   157.69  -2209.56
(empty) (empty) Fairview 615108   250.84  -1938.26		(empty) (empty) Fairview 615108   250.84  -1938.26
(empty) (empty) Fairview 221084   219.03  -1872.47		(empty) (empty) Fairview 221084   219.03  -1872.47
(empty) (empty) Fairview 677429     0.00  -1440.47		(empty) (empty) Fairview 677429     0.00  -1440.47
(empty) (empty) Fairview 668190     0.00    381.64		(empty) (empty) Fairview 668190     0.00    381.64
(empty) (empty) Fairview 431389    28.44    642.55		(empty) (empty) Fairview 431389    28.44    642.55
(empty) (empty) Fairview 523182     0.00   1970.36		(empty) (empty) Fairview 523182     0.00   1970.36
(empty) (empty) Fairview 629319  3114.92   2259.97		(empty) (empty) Fairview 629319  3114.92   2259.97
(empty) (empty)   Midway 704581  2249.48 -29647.22		(empty) (empty)   Midway 704581  2249.48 -29647.22
(empty) (empty)   Midway 309835  4362.47 -28135.82		(empty) (empty)   Midway 309835  4362.47 -28135.82
(empty) (empty)   Midway 160471  2843.14 -27012.42		(empty) (empty)   Midway 160471  2843.14 -27012.42
(empty) (empty)   Midway 527560  5212.42 -25961.49		(empty) (empty)   Midway 527560  5212.42 -25961.49
(empty) (empty)   Midway 473650   447.20 -25242.64		(empty) (empty)   Midway 473650   447.20 -25242.64
(empty) (empty)   Midway  56946   207.83 -22868.27		(empty) (empty)   Midway  56946   207.83 -22868.27
(empty) (empty)   Midway 412140   915.61 -22627.05		(empty) (empty)   Midway 412140   915.61 -22627.05
(empty) (empty)   Midway 100595  5776.64 -21019.32		(empty) (empty)   Midway 100595  5776.64 -21019.32
(empty) (empty)   Midway 185078  4934.65 -20391.29		(empty) (empty)   Midway 185078  4934.65 -20391.29
(empty) (empty)   Midway 656896  4485.23 -19381.12		(empty) (empty)   Midway 656896  4485.23 -19381.12
(empty) (empty)   Midway 684741  4834.81 -19361.50		(empty) (empty)   Midway 684741  4834.81 -19361.50
(empty) (empty)   Midway 167678  4095.86 -19357.39	      <
(empty) (empty)   Midway 586368  7348.62 -18664.35		(empty) (empty)   Midway 586368  7348.62 -18664.35
(empty) (empty)   Midway 162030    26.92 -18543.42		(empty) (empty)   Midway 162030    26.92 -18543.42
(empty) (empty)   Midway 663455  7909.05 -17561.60		(empty) (empty)   Midway 663455  7909.05 -17561.60
(empty) (empty)   Midway 169656 12649.34 -16572.91		(empty) (empty)   Midway 169656 12649.34 -16572.91
(empty) (empty)   Midway 386511  1690.14 -16512.42		(empty) (empty)   Midway 386511  1690.14 -16512.42
							      >	(empty) (empty)   Midway 393134  4941.92 -16407.34
							      >	(empty) (empty)   Midway 215618  9229.81 -16405.85
							      >	(empty) (empty)   Midway 437287   875.70 -16121.16
							      >	(empty) (empty)   Midway 646771  2627.38 -15879.80