resources/tpcds-modifiedQueries/q3.sql

0001 -- start query 3 in stream 0 using template query3.tpl
0002 select
0003   dt.d_year,
0004   item.i_brand_id brand_id,
0005   item.i_brand brand,
0006   sum(ss_net_profit) sum_agg
0007 from
0008   date_dim dt,
0009   store_sales,
0010   item
0011 where
0012   dt.d_date_sk = store_sales.ss_sold_date_sk
0013   and store_sales.ss_item_sk = item.i_item_sk
0014   and item.i_manufact_id = 436
0015   and dt.d_moy = 12
0016   -- partition key filters
0017   and (
0018 ss_sold_date_sk between 2415355 and 2415385
0019 or ss_sold_date_sk between 2415720 and 2415750
0020 or ss_sold_date_sk between 2416085 and 2416115
0021 or ss_sold_date_sk between 2416450 and 2416480
0022 or ss_sold_date_sk between 2416816 and 2416846
0023 or ss_sold_date_sk between 2417181 and 2417211
0024 or ss_sold_date_sk between 2417546 and 2417576
0025 or ss_sold_date_sk between 2417911 and 2417941
0026 or ss_sold_date_sk between 2418277 and 2418307
0027 or ss_sold_date_sk between 2418642 and 2418672
0028 or ss_sold_date_sk between 2419007 and 2419037
0029 or ss_sold_date_sk between 2419372 and 2419402
0030 or ss_sold_date_sk between 2419738 and 2419768
0031 or ss_sold_date_sk between 2420103 and 2420133
0032 or ss_sold_date_sk between 2420468 and 2420498
0033 or ss_sold_date_sk between 2420833 and 2420863
0034 or ss_sold_date_sk between 2421199 and 2421229
0035 or ss_sold_date_sk between 2421564 and 2421594
0036 or ss_sold_date_sk between 2421929 and 2421959
0037 or ss_sold_date_sk between 2422294 and 2422324
0038 or ss_sold_date_sk between 2422660 and 2422690
0039 or ss_sold_date_sk between 2423025 and 2423055
0040 or ss_sold_date_sk between 2423390 and 2423420
0041 or ss_sold_date_sk between 2423755 and 2423785
0042 or ss_sold_date_sk between 2424121 and 2424151
0043 or ss_sold_date_sk between 2424486 and 2424516
0044 or ss_sold_date_sk between 2424851 and 2424881
0045 or ss_sold_date_sk between 2425216 and 2425246
0046 or ss_sold_date_sk between 2425582 and 2425612
0047 or ss_sold_date_sk between 2425947 and 2425977
0048 or ss_sold_date_sk between 2426312 and 2426342
0049 or ss_sold_date_sk between 2426677 and 2426707
0050 or ss_sold_date_sk between 2427043 and 2427073
0051 or ss_sold_date_sk between 2427408 and 2427438
0052 or ss_sold_date_sk between 2427773 and 2427803
0053 or ss_sold_date_sk between 2428138 and 2428168
0054 or ss_sold_date_sk between 2428504 and 2428534
0055 or ss_sold_date_sk between 2428869 and 2428899
0056 or ss_sold_date_sk between 2429234 and 2429264
0057 or ss_sold_date_sk between 2429599 and 2429629
0058 or ss_sold_date_sk between 2429965 and 2429995
0059 or ss_sold_date_sk between 2430330 and 2430360
0060 or ss_sold_date_sk between 2430695 and 2430725
0061 or ss_sold_date_sk between 2431060 and 2431090
0062 or ss_sold_date_sk between 2431426 and 2431456
0063 or ss_sold_date_sk between 2431791 and 2431821
0064 or ss_sold_date_sk between 2432156 and 2432186
0065 or ss_sold_date_sk between 2432521 and 2432551
0066 or ss_sold_date_sk between 2432887 and 2432917
0067 or ss_sold_date_sk between 2433252 and 2433282
0068 or ss_sold_date_sk between 2433617 and 2433647
0069 or ss_sold_date_sk between 2433982 and 2434012
0070 or ss_sold_date_sk between 2434348 and 2434378
0071 or ss_sold_date_sk between 2434713 and 2434743
0072 or ss_sold_date_sk between 2435078 and 2435108
0073 or ss_sold_date_sk between 2435443 and 2435473
0074 or ss_sold_date_sk between 2435809 and 2435839
0075 or ss_sold_date_sk between 2436174 and 2436204
0076 or ss_sold_date_sk between 2436539 and 2436569
0077 or ss_sold_date_sk between 2436904 and 2436934
0078 or ss_sold_date_sk between 2437270 and 2437300
0079 or ss_sold_date_sk between 2437635 and 2437665
0080 or ss_sold_date_sk between 2438000 and 2438030
0081 or ss_sold_date_sk between 2438365 and 2438395
0082 or ss_sold_date_sk between 2438731 and 2438761
0083 or ss_sold_date_sk between 2439096 and 2439126
0084 or ss_sold_date_sk between 2439461 and 2439491
0085 or ss_sold_date_sk between 2439826 and 2439856
0086 or ss_sold_date_sk between 2440192 and 2440222
0087 or ss_sold_date_sk between 2440557 and 2440587
0088 or ss_sold_date_sk between 2440922 and 2440952
0089 or ss_sold_date_sk between 2441287 and 2441317
0090 or ss_sold_date_sk between 2441653 and 2441683
0091 or ss_sold_date_sk between 2442018 and 2442048
0092 or ss_sold_date_sk between 2442383 and 2442413
0093 or ss_sold_date_sk between 2442748 and 2442778
0094 or ss_sold_date_sk between 2443114 and 2443144
0095 or ss_sold_date_sk between 2443479 and 2443509
0096 or ss_sold_date_sk between 2443844 and 2443874
0097 or ss_sold_date_sk between 2444209 and 2444239
0098 or ss_sold_date_sk between 2444575 and 2444605
0099 or ss_sold_date_sk between 2444940 and 2444970
0100 or ss_sold_date_sk between 2445305 and 2445335
0101 or ss_sold_date_sk between 2445670 and 2445700
0102 or ss_sold_date_sk between 2446036 and 2446066
0103 or ss_sold_date_sk between 2446401 and 2446431
0104 or ss_sold_date_sk between 2446766 and 2446796
0105 or ss_sold_date_sk between 2447131 and 2447161
0106 or ss_sold_date_sk between 2447497 and 2447527
0107 or ss_sold_date_sk between 2447862 and 2447892
0108 or ss_sold_date_sk between 2448227 and 2448257
0109 or ss_sold_date_sk between 2448592 and 2448622
0110 or ss_sold_date_sk between 2448958 and 2448988
0111 or ss_sold_date_sk between 2449323 and 2449353
0112 or ss_sold_date_sk between 2449688 and 2449718
0113 or ss_sold_date_sk between 2450053 and 2450083
0114 or ss_sold_date_sk between 2450419 and 2450449
0115 or ss_sold_date_sk between 2450784 and 2450814
0116 or ss_sold_date_sk between 2451149 and 2451179
0117 or ss_sold_date_sk between 2451514 and 2451544
0118 or ss_sold_date_sk between 2451880 and 2451910
0119 or ss_sold_date_sk between 2452245 and 2452275
0120 or ss_sold_date_sk between 2452610 and 2452640
0121 or ss_sold_date_sk between 2452975 and 2453005
0122 or ss_sold_date_sk between 2453341 and 2453371
0123 or ss_sold_date_sk between 2453706 and 2453736
0124 or ss_sold_date_sk between 2454071 and 2454101
0125 or ss_sold_date_sk between 2454436 and 2454466
0126 or ss_sold_date_sk between 2454802 and 2454832
0127 or ss_sold_date_sk between 2455167 and 2455197
0128 or ss_sold_date_sk between 2455532 and 2455562
0129 or ss_sold_date_sk between 2455897 and 2455927
0130 or ss_sold_date_sk between 2456263 and 2456293
0131 or ss_sold_date_sk between 2456628 and 2456658
0132 or ss_sold_date_sk between 2456993 and 2457023
0133 or ss_sold_date_sk between 2457358 and 2457388
0134 or ss_sold_date_sk between 2457724 and 2457754
0135 or ss_sold_date_sk between 2458089 and 2458119
0136 or ss_sold_date_sk between 2458454 and 2458484
0137 or ss_sold_date_sk between 2458819 and 2458849
0138 or ss_sold_date_sk between 2459185 and 2459215
0139 or ss_sold_date_sk between 2459550 and 2459580
0140 or ss_sold_date_sk between 2459915 and 2459945
0141 or ss_sold_date_sk between 2460280 and 2460310
0142 or ss_sold_date_sk between 2460646 and 2460676
0143 or ss_sold_date_sk between 2461011 and 2461041
0144 or ss_sold_date_sk between 2461376 and 2461406
0145 or ss_sold_date_sk between 2461741 and 2461771
0146 or ss_sold_date_sk between 2462107 and 2462137
0147 or ss_sold_date_sk between 2462472 and 2462502
0148 or ss_sold_date_sk between 2462837 and 2462867
0149 or ss_sold_date_sk between 2463202 and 2463232
0150 or ss_sold_date_sk between 2463568 and 2463598
0151 or ss_sold_date_sk between 2463933 and 2463963
0152 or ss_sold_date_sk between 2464298 and 2464328
0153 or ss_sold_date_sk between 2464663 and 2464693
0154 or ss_sold_date_sk between 2465029 and 2465059
0155 or ss_sold_date_sk between 2465394 and 2465424
0156 or ss_sold_date_sk between 2465759 and 2465789
0157 or ss_sold_date_sk between 2466124 and 2466154
0158 or ss_sold_date_sk between 2466490 and 2466520
0159 or ss_sold_date_sk between 2466855 and 2466885
0160 or ss_sold_date_sk between 2467220 and 2467250
0161 or ss_sold_date_sk between 2467585 and 2467615
0162 or ss_sold_date_sk between 2467951 and 2467981
0163 or ss_sold_date_sk between 2468316 and 2468346
0164 or ss_sold_date_sk between 2468681 and 2468711
0165 or ss_sold_date_sk between 2469046 and 2469076
0166 or ss_sold_date_sk between 2469412 and 2469442
0167 or ss_sold_date_sk between 2469777 and 2469807
0168 or ss_sold_date_sk between 2470142 and 2470172
0169 or ss_sold_date_sk between 2470507 and 2470537
0170 or ss_sold_date_sk between 2470873 and 2470903
0171 or ss_sold_date_sk between 2471238 and 2471268
0172 or ss_sold_date_sk between 2471603 and 2471633
0173 or ss_sold_date_sk between 2471968 and 2471998
0174 or ss_sold_date_sk between 2472334 and 2472364
0175 or ss_sold_date_sk between 2472699 and 2472729
0176 or ss_sold_date_sk between 2473064 and 2473094
0177 or ss_sold_date_sk between 2473429 and 2473459
0178 or ss_sold_date_sk between 2473795 and 2473825
0179 or ss_sold_date_sk between 2474160 and 2474190
0180 or ss_sold_date_sk between 2474525 and 2474555
0181 or ss_sold_date_sk between 2474890 and 2474920
0182 or ss_sold_date_sk between 2475256 and 2475286
0183 or ss_sold_date_sk between 2475621 and 2475651
0184 or ss_sold_date_sk between 2475986 and 2476016
0185 or ss_sold_date_sk between 2476351 and 2476381
0186 or ss_sold_date_sk between 2476717 and 2476747
0187 or ss_sold_date_sk between 2477082 and 2477112
0188 or ss_sold_date_sk between 2477447 and 2477477
0189 or ss_sold_date_sk between 2477812 and 2477842
0190 or ss_sold_date_sk between 2478178 and 2478208
0191 or ss_sold_date_sk between 2478543 and 2478573
0192 or ss_sold_date_sk between 2478908 and 2478938
0193 or ss_sold_date_sk between 2479273 and 2479303
0194 or ss_sold_date_sk between 2479639 and 2479669
0195 or ss_sold_date_sk between 2480004 and 2480034
0196 or ss_sold_date_sk between 2480369 and 2480399
0197 or ss_sold_date_sk between 2480734 and 2480764
0198 or ss_sold_date_sk between 2481100 and 2481130
0199 or ss_sold_date_sk between 2481465 and 2481495
0200 or ss_sold_date_sk between 2481830 and 2481860
0201 or ss_sold_date_sk between 2482195 and 2482225
0202 or ss_sold_date_sk between 2482561 and 2482591
0203 or ss_sold_date_sk between 2482926 and 2482956
0204 or ss_sold_date_sk between 2483291 and 2483321
0205 or ss_sold_date_sk between 2483656 and 2483686
0206 or ss_sold_date_sk between 2484022 and 2484052
0207 or ss_sold_date_sk between 2484387 and 2484417
0208 or ss_sold_date_sk between 2484752 and 2484782
0209 or ss_sold_date_sk between 2485117 and 2485147
0210 or ss_sold_date_sk between 2485483 and 2485513
0211 or ss_sold_date_sk between 2485848 and 2485878
0212 or ss_sold_date_sk between 2486213 and 2486243
0213 or ss_sold_date_sk between 2486578 and 2486608
0214 or ss_sold_date_sk between 2486944 and 2486974
0215 or ss_sold_date_sk between 2487309 and 2487339
0216 or ss_sold_date_sk between 2487674 and 2487704
0217 or ss_sold_date_sk between 2488039 and 2488069
0218 )
0219 group by
0220   dt.d_year,
0221   item.i_brand,
0222   item.i_brand_id
0223 order by
0224   dt.d_year,
0225   sum_agg desc,
0226   brand_id
0227 limit 100
0228 -- end query 3 in stream 0 using template query3.tpl