| ==== |
| ---- QUERY: TPCDS-Q21 |
| select * |
| from(select w_warehouse_name |
| ,i_item_id |
| ,sum(case when (cast(d_date as timestamp) < cast ('2000-03-11' as timestamp)) |
| then inv_quantity_on_hand |
| else 0 end) as inv_before |
| ,sum(case when (cast(d_date as timestamp) >= cast ('2000-03-11' as timestamp)) |
| then inv_quantity_on_hand |
| else 0 end) as inv_after |
| from inventory |
| ,warehouse |
| ,item |
| ,date_dim |
| where i_current_price between 0.99 and 1.49 |
| and i_item_sk = inv_item_sk |
| and inv_warehouse_sk = w_warehouse_sk |
| and inv_date_sk = d_date_sk |
| and cast(d_date as timestamp) between (cast ('2000-03-11' as timestamp) - interval 30 days) |
| and (cast ('2000-03-11' as timestamp) + interval 30 days) |
| group by w_warehouse_name, i_item_id) x |
| where (case when inv_before > 0 |
| then inv_after / inv_before |
| else null |
| end) between 2.0/3.0 and 3.0/2.0 |
| order by w_warehouse_name |
| ,i_item_id |
| limit 100; |
| ---- RESULTS |
| 'Bad cards must make.','AAAAAAAAAACDAAAA',1889,2168 |
| 'Bad cards must make.','AAAAAAAAAAHDAAAA',2739,2039 |
| 'Bad cards must make.','AAAAAAAAABDAAAAA',1717,1782 |
| 'Bad cards must make.','AAAAAAAAACGCAAAA',2296,2276 |
| 'Bad cards must make.','AAAAAAAAACKCAAAA',2443,1878 |
| 'Bad cards must make.','AAAAAAAAACOBAAAA',2705,2428 |
| 'Bad cards must make.','AAAAAAAAADGBAAAA',2242,2759 |
| 'Bad cards must make.','AAAAAAAAADKBAAAA',2138,2456 |
| 'Bad cards must make.','AAAAAAAAAEADAAAA',2914,2237 |
| 'Bad cards must make.','AAAAAAAAAEOCAAAA',1797,2073 |
| 'Bad cards must make.','AAAAAAAAAFACAAAA',2058,2734 |
| 'Bad cards must make.','AAAAAAAAAFADAAAA',2173,2515 |
| 'Bad cards must make.','AAAAAAAAAFDCAAAA',2309,2277 |
| 'Bad cards must make.','AAAAAAAAAGIDAAAA',1864,2197 |
| 'Bad cards must make.','AAAAAAAAAHDEAAAA',2483,2077 |
| 'Bad cards must make.','AAAAAAAAAHJBAAAA',2904,3312 |
| 'Bad cards must make.','AAAAAAAAAIECAAAA',2239,2526 |
| 'Bad cards must make.','AAAAAAAAAIMCAAAA',1948,2693 |
| 'Bad cards must make.','AAAAAAAAAJKBAAAA',2184,2190 |
| 'Bad cards must make.','AAAAAAAAALECAAAA',2539,3052 |
| 'Bad cards must make.','AAAAAAAAALNBAAAA',1406,1728 |
| 'Bad cards must make.','AAAAAAAAAMPBAAAA',3450,3033 |
| 'Bad cards must make.','AAAAAAAAANFEAAAA',2255,1517 |
| 'Bad cards must make.','AAAAAAAAAPLAAAAA',1465,1227 |
| 'Bad cards must make.','AAAAAAAABBIDAAAA',2822,2125 |
| 'Bad cards must make.','AAAAAAAABDCEAAAA',2068,2187 |
| 'Bad cards must make.','AAAAAAAABEFDAAAA',1023,1510 |
| 'Bad cards must make.','AAAAAAAABFNAAAAA',1962,2719 |
| 'Bad cards must make.','AAAAAAAABFOCAAAA',2392,2520 |
| 'Bad cards must make.','AAAAAAAABHPCAAAA',2945,3058 |
| 'Bad cards must make.','AAAAAAAABIDBAAAA',1746,1706 |
| 'Bad cards must make.','AAAAAAAABJFEAAAA',2458,2647 |
| 'Bad cards must make.','AAAAAAAABKFAAAAA',3152,2529 |
| 'Bad cards must make.','AAAAAAAABLDEAAAA',2439,2899 |
| 'Bad cards must make.','AAAAAAAABNHBAAAA',2254,2117 |
| 'Bad cards must make.','AAAAAAAABNLAAAAA',2552,2608 |
| 'Bad cards must make.','AAAAAAAABNLDAAAA',3103,2489 |
| 'Bad cards must make.','AAAAAAAABNMCAAAA',1752,2078 |
| 'Bad cards must make.','AAAAAAAABOCCAAAA',1697,1547 |
| 'Bad cards must make.','AAAAAAAACAAAAAAA',2736,1937 |
| 'Bad cards must make.','AAAAAAAACAJCAAAA',1921,1806 |
| 'Bad cards must make.','AAAAAAAACBFAAAAA',1237,1460 |
| 'Bad cards must make.','AAAAAAAACBIAAAAA',2157,1915 |
| 'Bad cards must make.','AAAAAAAACBPBAAAA',3220,2970 |
| 'Bad cards must make.','AAAAAAAACCABAAAA',1167,1704 |
| 'Bad cards must make.','AAAAAAAACCMDAAAA',2057,2563 |
| 'Bad cards must make.','AAAAAAAACDBCAAAA',2017,1494 |
| 'Bad cards must make.','AAAAAAAACGFDAAAA',2811,3027 |
| 'Bad cards must make.','AAAAAAAACHHDAAAA',2589,1935 |
| 'Bad cards must make.','AAAAAAAACJDCAAAA',1299,1807 |
| 'Bad cards must make.','AAAAAAAACKHAAAAA',1484,2158 |
| 'Bad cards must make.','AAAAAAAACLKAAAAA',2743,2910 |
| 'Bad cards must make.','AAAAAAAACMJAAAAA',1345,1776 |
| 'Bad cards must make.','AAAAAAAACMLAAAAA',1869,2372 |
| 'Bad cards must make.','AAAAAAAACMPDAAAA',1480,2177 |
| 'Bad cards must make.','AAAAAAAACNBBAAAA',2914,2373 |
| 'Bad cards must make.','AAAAAAAACNEBAAAA',2170,2456 |
| 'Bad cards must make.','AAAAAAAACNFAAAAA',2854,2071 |
| 'Bad cards must make.','AAAAAAAACNGBAAAA',1830,2659 |
| 'Bad cards must make.','AAAAAAAACNHDAAAA',1507,1643 |
| 'Bad cards must make.','AAAAAAAACNPCAAAA',2756,2402 |
| 'Bad cards must make.','AAAAAAAADCBEAAAA',2403,2684 |
| 'Bad cards must make.','AAAAAAAADCPCAAAA',2112,1881 |
| 'Bad cards must make.','AAAAAAAADDNDAAAA',3404,2652 |
| 'Bad cards must make.','AAAAAAAADEJDAAAA',1719,1822 |
| 'Bad cards must make.','AAAAAAAADFLAAAAA',3242,2344 |
| 'Bad cards must make.','AAAAAAAADGCCAAAA',2435,2159 |
| 'Bad cards must make.','AAAAAAAADGFCAAAA',2350,1867 |
| 'Bad cards must make.','AAAAAAAADGGBAAAA',2686,3567 |
| 'Bad cards must make.','AAAAAAAADGKAAAAA',2927,2595 |
| 'Bad cards must make.','AAAAAAAADHPAAAAA',2639,2216 |
| 'Bad cards must make.','AAAAAAAADKPDAAAA',2476,3512 |
| 'Bad cards must make.','AAAAAAAADMBAAAAA',2472,2301 |
| 'Bad cards must make.','AAAAAAAADNLBAAAA',2792,2077 |
| 'Bad cards must make.','AAAAAAAADPABAAAA',1719,1667 |
| 'Bad cards must make.','AAAAAAAAEAICAAAA',1420,2094 |
| 'Bad cards must make.','AAAAAAAAEAPAAAAA',1875,2244 |
| 'Bad cards must make.','AAAAAAAAEBCEAAAA',2241,1924 |
| 'Bad cards must make.','AAAAAAAAEBPCAAAA',2544,2281 |
| 'Bad cards must make.','AAAAAAAAECGCAAAA',2039,1848 |
| 'Bad cards must make.','AAAAAAAAEDOCAAAA',2401,1770 |
| 'Bad cards must make.','AAAAAAAAEDPDAAAA',3041,2119 |
| 'Bad cards must make.','AAAAAAAAEGGBAAAA',2717,2032 |
| 'Bad cards must make.','AAAAAAAAEGJAAAAA',2996,2092 |
| 'Bad cards must make.','AAAAAAAAEGMAAAAA',3051,2982 |
| 'Bad cards must make.','AAAAAAAAEGOCAAAA',2086,1958 |
| 'Bad cards must make.','AAAAAAAAEHECAAAA',1993,1498 |
| 'Bad cards must make.','AAAAAAAAEHFDAAAA',2217,2151 |
| 'Bad cards must make.','AAAAAAAAEHLBAAAA',1417,2034 |
| 'Bad cards must make.','AAAAAAAAEIDEAAAA',1804,1474 |
| 'Bad cards must make.','AAAAAAAAEJAEAAAA',2287,2006 |
| 'Bad cards must make.','AAAAAAAAEJJDAAAA',2080,1660 |
| 'Bad cards must make.','AAAAAAAAEJPDAAAA',1788,2188 |
| 'Bad cards must make.','AAAAAAAAEKCAAAAA',2451,2182 |
| 'Bad cards must make.','AAAAAAAAENFBAAAA',2123,2294 |
| 'Bad cards must make.','AAAAAAAAEONAAAAA',2706,1887 |
| 'Bad cards must make.','AAAAAAAAEPHCAAAA',1332,1209 |
| 'Bad cards must make.','AAAAAAAAEPKCAAAA',2445,1824 |
| 'Bad cards must make.','AAAAAAAAFAKBAAAA',843,1256 |
| 'Bad cards must make.','AAAAAAAAFALDAAAA',2391,2019 |
| ---- TYPES |
| STRING, STRING, BIGINT, BIGINT |
| ==== |
