| ==== |
| ---- QUERY: TPCDS-Q47 |
| with v1 as( |
| select i_category, i_brand, |
| s_store_name, s_company_name, |
| d_year, d_moy, |
| sum(ss_sales_price) sum_sales, |
| avg(sum(ss_sales_price)) over |
| (partition by i_category, i_brand, |
| s_store_name, s_company_name, d_year) |
| avg_monthly_sales, |
| rank() over |
| (partition by i_category, i_brand, |
| s_store_name, s_company_name |
| order by d_year, d_moy) rn |
| from item, store_sales, date_dim, store |
| where ss_item_sk = i_item_sk and |
| ss_sold_date_sk = d_date_sk and |
| ss_store_sk = s_store_sk and |
| ( |
| d_year = 2000 or |
| ( d_year = 2000-1 and d_moy =12) or |
| ( d_year = 2000+1 and d_moy =1) |
| ) |
| group by i_category, i_brand, |
| s_store_name, s_company_name, |
| d_year, d_moy), |
| v2 as( |
| select v1.i_category |
| ,v1.d_year, v1.d_moy |
| ,v1.avg_monthly_sales |
| ,v1.sum_sales, v1_lag.sum_sales psum, v1_lead.sum_sales nsum |
| from v1, v1 v1_lag, v1 v1_lead |
| where v1.i_category = v1_lag.i_category and |
| v1.i_category = v1_lead.i_category and |
| v1.i_brand = v1_lag.i_brand and |
| v1.i_brand = v1_lead.i_brand and |
| v1.s_store_name = v1_lag.s_store_name and |
| v1.s_store_name = v1_lead.s_store_name and |
| v1.s_company_name = v1_lag.s_company_name and |
| v1.s_company_name = v1_lead.s_company_name and |
| v1.rn = v1_lag.rn + 1 and |
| v1.rn = v1_lead.rn - 1) |
| select * |
| from v2 |
| where d_year = 2000 and |
| avg_monthly_sales > 0 and |
| case when avg_monthly_sales > 0 then abs(sum_sales - avg_monthly_sales) / avg_monthly_sales else null end > 0.1 |
| order by sum_sales - avg_monthly_sales, 3 |
| limit 100; |
| ---- RESULTS |
| 'Women',2000,6,5167.868333,1702.17,3236.87,3399.13 |
| 'Music',2000,4,5036.090833,1860.99,2706.82,3303.43 |
| 'Children',2000,3,5064.604167,1912.86,3298.07,2757.48 |
| 'Women',2000,3,4913.032500,1779.75,2390.63,2257.04 |
| 'Shoes',2000,7,5168.767500,2069.14,2983.37,8035.14 |
| 'Music',2000,3,5110.744167,2022.81,2461.84,3067.59 |
| 'Music',2000,5,4803.555833,1743.74,2778.83,3066.97 |
| 'Children',2000,7,5175.091667,2116.90,3610.19,6870.27 |
| 'Men',2000,4,4937.400833,1880.07,3078.36,2756.00 |
| 'Women',2000,3,4958.763333,1958.78,2678.23,3027.08 |
| 'Music',2000,3,5212.675833,2215.63,3321.39,3357.05 |
| 'Women',2000,6,4950.270000,1956.92,2980.12,2196.47 |
| 'Shoes',2000,6,4926.845000,1942.09,2679.25,3503.44 |
| 'Women',2000,5,4606.794167,1639.46,2157.87,2583.06 |
| 'Women',2000,1,4997.976667,2033.94,4136.88,3386.81 |
| 'Music',2000,3,5212.355000,2256.79,2483.30,3203.46 |
| 'Shoes',2000,3,5326.516667,2380.52,3557.54,2381.65 |
| 'Shoes',2000,4,5326.516667,2381.65,2380.52,3185.08 |
| 'Music',2000,6,5076.010833,2166.79,2636.43,2696.26 |
| 'Shoes',2000,5,5213.916667,2308.61,3484.35,2631.62 |
| 'Shoes',2000,7,4944.166667,2043.50,2714.17,7158.49 |
| 'Children',2000,6,5109.577500,2231.87,2492.85,2716.69 |
| 'Music',2000,7,5036.090833,2162.98,2169.49,7442.03 |
| 'Music',2000,6,5036.090833,2169.49,3303.43,2162.98 |
| 'Music',2000,3,4953.792500,2087.58,2881.98,2396.23 |
| 'Shoes',2000,3,4824.920833,1981.97,2557.91,2178.72 |
| 'Music',2000,4,5270.568333,2429.81,2500.28,3121.27 |
| 'Women',2000,4,4390.827500,1562.28,2203.54,2814.98 |
| 'Shoes',2000,2,4565.836667,1740.10,3299.55,2905.54 |
| 'Women',2000,4,4950.270000,2125.36,3688.19,2980.12 |
| 'Men',2000,7,4657.830000,1836.07,2432.42,6304.84 |
| 'Shoes',2000,6,5094.271667,2285.80,2982.59,2944.67 |
| 'Shoes',2000,5,5233.675000,2458.45,3530.35,4587.71 |
| 'Music',2000,3,5270.568333,2500.28,3185.98,2429.81 |
| 'Music',2000,3,5257.115000,2491.28,2494.71,2963.86 |
| 'Music',2000,2,5257.115000,2494.71,2788.64,2491.28 |
| 'Shoes',2000,4,5168.767500,2407.05,3240.95,2483.46 |
| 'Women',2000,3,4422.386667,1666.68,2570.10,2097.53 |
| 'Women',2000,7,4950.270000,2196.47,1956.92,7068.51 |
| 'Men',2000,5,5016.401667,2267.73,2984.52,3051.68 |
| 'Music',2000,4,5076.010833,2328.83,3139.50,2636.43 |
| 'Men',2000,4,4657.830000,1925.05,2409.04,2999.06 |
| 'Music',2000,2,4931.629167,2199.12,3942.13,2335.05 |
| 'Music',2000,2,5212.355000,2483.30,4582.12,2256.79 |
| 'Men',2000,3,4872.140833,2143.87,3353.30,2688.70 |
| 'Music',2000,7,5212.355000,2487.88,2624.81,6788.15 |
| 'Shoes',2000,7,4912.345000,2194.71,3320.16,7019.04 |
| 'Women',2000,4,4521.217500,1803.95,2858.45,2451.32 |
| 'Music',2000,7,5257.115000,2540.19,3558.30,7261.59 |
| 'Women',2000,2,5183.971667,2471.71,3493.02,2920.36 |
| 'Shoes',2000,3,4440.288333,1737.33,1857.98,2951.57 |
| 'Women',2000,4,5167.868333,2468.50,3375.26,3236.87 |
| 'Shoes',2000,2,4732.806667,2035.56,3540.84,2823.10 |
| 'Music',2000,2,4960.848333,2267.45,3486.05,3329.26 |
| 'Shoes',2000,2,5240.653333,2549.66,3081.94,2798.03 |
| 'Shoes',2000,5,5168.767500,2483.46,2407.05,2983.37 |
| 'Women',2000,5,4888.695833,2204.11,3149.43,2688.11 |
| 'Music',2000,7,5270.568333,2587.41,3050.17,6970.69 |
| 'Children',2000,7,4372.639167,1694.02,2257.72,6000.24 |
| 'Music',2000,4,4916.410000,2240.65,3382.64,3182.24 |
| 'Men',2000,6,4364.737500,1689.53,2992.20,3120.76 |
| 'Shoes',2000,4,5090.508333,2415.99,3256.81,2925.27 |
| 'Music',2000,4,4813.664167,2143.57,2637.02,2902.70 |
| 'Men',2000,5,4595.062500,1926.36,2586.96,2922.71 |
| 'Women',2000,4,4913.032500,2257.04,1779.75,2643.30 |
| 'Men',2000,4,4371.782500,1718.21,2208.02,2721.25 |
| 'Music',2000,2,5110.744167,2461.84,4401.88,2022.81 |
| 'Women',2000,3,5192.789167,2544.73,3587.53,3197.85 |
| 'Shoes',2000,4,4824.920833,2178.72,1981.97,2884.49 |
| 'Shoes',2000,3,5233.675000,2588.01,3532.78,3530.35 |
| 'Children',2000,5,4483.991667,1838.66,2619.28,2374.55 |
| 'Music',2000,7,5043.647500,2403.81,3238.49,6835.65 |
| 'Women',2000,7,4407.061667,1776.42,2981.25,5710.21 |
| 'Men',2000,6,4830.246667,2199.80,2412.75,3006.00 |
| 'Shoes',2000,6,5326.516667,2701.75,3185.08,3285.18 |
| 'Music',2000,2,4916.410000,2298.33,3493.76,3382.64 |
| 'Women',2000,5,4727.370000,2110.50,2519.62,2267.53 |
| 'Children',2000,5,5109.577500,2492.85,2728.55,2231.87 |
| 'Music',2000,4,5043.647500,2430.73,2644.32,3481.08 |
| 'Music',2000,6,4131.199167,1519.23,2157.93,2655.31 |
| 'Music',2000,2,5172.156667,2563.98,2905.94,3777.26 |
| 'Music',2000,4,4931.629167,2327.08,2335.05,2882.04 |
| 'Music',2000,5,4960.848333,2360.20,2406.42,3555.36 |
| 'Shoes',2000,4,4823.300000,2222.80,3016.25,2462.91 |
| 'Music',2000,3,4931.629167,2335.05,2199.12,2327.08 |
| 'Music',2000,6,5212.355000,2624.81,2899.34,2487.88 |
| 'Men',2000,3,5016.401667,2432.36,3088.82,2984.52 |
| 'Shoes',2000,2,4440.288333,1857.98,3346.62,1737.33 |
| 'Shoes',2000,6,5213.916667,2631.62,2308.61,3081.21 |
| 'Music',2000,6,5000.816667,2419.42,3117.07,3086.00 |
| 'Children',2000,3,4551.438333,1973.72,2083.41,2621.49 |
| 'Music',2000,7,5212.675833,2638.50,3720.45,7180.32 |
| 'Women',2000,2,5092.845000,2525.38,3066.60,2819.83 |
| 'Shoes',2000,2,5213.916667,2649.38,3206.27,3351.30 |
| 'Men',2000,1,4595.062500,2032.46,3557.44,2372.72 |
| 'Shoes',2000,7,5032.343333,2469.75,2961.97,6219.49 |
| 'Women',2000,6,4776.425000,2216.70,2861.21,3620.51 |
| 'Women',2000,7,4727.370000,2168.76,2267.53,7819.76 |
| 'Music',2000,4,4953.792500,2396.23,2087.58,2921.95 |
| 'Children',2000,5,5206.238333,2649.03,2879.82,3447.77 |
| ---- TYPES |
| STRING, INT, INT, DECIMAL, DECIMAL, DECIMAL, DECIMAL |
| ==== |
