| ==== |
| ---- QUERY: TPCDS-Q78 |
| -- RESULT MISMATCH FROM ORIGINAL |
| -- FIXED. TAKE ACTUAL RESULT AS EXPECTED |
| with ws as |
| (select d_year AS ws_sold_year, ws_item_sk, |
| ws_bill_customer_sk ws_customer_sk, |
| sum(ws_quantity) ws_qty, |
| sum(ws_wholesale_cost) ws_wc, |
| sum(ws_sales_price) ws_sp |
| from web_sales |
| left join web_returns on wr_order_number=ws_order_number and ws_item_sk=wr_item_sk |
| join date_dim on ws_sold_date_sk = d_date_sk |
| where wr_order_number is null |
| group by d_year, ws_item_sk, ws_bill_customer_sk |
| ), |
| cs as |
| (select d_year AS cs_sold_year, cs_item_sk, |
| cs_bill_customer_sk cs_customer_sk, |
| sum(cs_quantity) cs_qty, |
| sum(cs_wholesale_cost) cs_wc, |
| sum(cs_sales_price) cs_sp |
| from catalog_sales |
| left join catalog_returns on cr_order_number=cs_order_number and cs_item_sk=cr_item_sk |
| join date_dim on cs_sold_date_sk = d_date_sk |
| where cr_order_number is null |
| group by d_year, cs_item_sk, cs_bill_customer_sk |
| ), |
| ss as |
| (select d_year AS ss_sold_year, ss_item_sk, |
| ss_customer_sk, |
| sum(ss_quantity) ss_qty, |
| sum(ss_wholesale_cost) ss_wc, |
| sum(ss_sales_price) ss_sp |
| from store_sales |
| left join store_returns on sr_ticket_number=ss_ticket_number and ss_item_sk=sr_item_sk |
| join date_dim on ss_sold_date_sk = d_date_sk |
| where sr_ticket_number is null |
| group by d_year, ss_item_sk, ss_customer_sk |
| ) |
| select |
| ss_sold_year, ss_item_sk, ss_customer_sk, |
| round(ss_qty/(coalesce(ws_qty,0)+coalesce(cs_qty,0)),2) ratio, |
| ss_qty store_qty, ss_wc store_wholesale_cost, ss_sp store_sales_price, |
| coalesce(ws_qty,0)+coalesce(cs_qty,0) other_chan_qty, |
| coalesce(ws_wc,0)+coalesce(cs_wc,0) other_chan_wholesale_cost, |
| coalesce(ws_sp,0)+coalesce(cs_sp,0) other_chan_sales_price |
| from ss |
| left join ws on (ws_sold_year=ss_sold_year and ws_item_sk=ss_item_sk and ws_customer_sk=ss_customer_sk) |
| left join cs on (cs_sold_year=ss_sold_year and cs_item_sk=ss_item_sk and cs_customer_sk=ss_customer_sk) |
| where (coalesce(ws_qty,0)>0 or coalesce(cs_qty, 0)>0) and ss_sold_year=2002 |
| order by |
| ss_sold_year, ss_item_sk, ss_customer_sk, |
| ss_qty desc, ss_wc desc, ss_sp desc, |
| other_chan_qty, |
| other_chan_wholesale_cost, |
| other_chan_sales_price, |
| round(ss_qty/(coalesce(ws_qty+cs_qty,1)),2) |
| limit 100; |
| ---- RESULTS |
| 2002,9,5646,0.43,20,7.84,11.54,47,30.81,85.03 |
| 2002,57,97133,6.36,70,40.21,43.02,11,5.16,11.10 |
| 2002,129,108,26.00,26,10.48,2.37,1,87.68,47.14 |
| 2002,321,41960,0.17,7,90.35,105.67,41,39.05,24.69 |
| 2002,511,14899,0.25,21,72.09,108.17,84,89.30,103.17 |
| 2002,690,2510,0.54,37,79.26,68.23,69,56.38,22.82 |
| 2002,721,403,1.67,95,61.68,65.23,57,52.67,50.53 |
| 2002,729,46247,2.09,73,91.24,89.89,35,80.58,35.77 |
| 2002,765,37957,0.06,5,84.16,14.01,90,12.45,5.09 |
| 2002,1059,25758,1.38,47,86.96,91.23,34,32.27,5.76 |
| 2002,1236,41346,1.59,43,28.56,40.90,27,7.18,2.01 |
| 2002,1260,46874,1.11,98,5.18,3.01,88,50.18,1.29 |
| 2002,1335,76341,2.94,97,86.95,3.30,33,68.26,73.11 |
| 2002,1347,76414,0.01,1,5.00,NULL,79,45.08,13.14 |
| 2002,1464,89045,1.03,100,95.99,NULL,97,38.98,27.96 |
| 2002,1477,17492,2.12,89,38.91,37.74,42,38.00,39.46 |
| 2002,1767,16602,0.76,16,94.71,47.81,21,12.73,15.51 |
| 2002,1983,47178,2.53,76,63.03,75.63,30,90.09,116.43 |
| 2002,1986,6198,0.72,31,71.27,97.49,43,44.13,79.14 |
| 2002,2071,24159,0.84,63,84.99,55.68,75,89.63,120.08 |
| 2002,2245,73119,0.09,6,10.04,2.59,65,61.47,44.42 |
| 2002,2293,27432,10.57,74,47.06,59.52,7,5.27,7.03 |
| 2002,2353,92698,1.41,58,85.54,11.20,41,41.20,86.32 |
| 2002,2761,38357,0.34,32,49.23,9.98,94,37.74,37.64 |
| 2002,2845,7790,1.73,76,85.75,30.56,44,93.91,0.00 |
| 2002,2959,47472,1.97,75,77.31,92.67,38,82.28,83.75 |
| 2002,3045,20740,0.51,25,97.93,88.86,49,70.74,157.56 |
| 2002,3129,26451,0.09,6,31.93,10.29,65,62.81,90.30 |
| 2002,3204,35986,1.88,32,12.65,3.84,17,64.95,44.36 |
| 2002,3219,29526,1.46,82,42.31,65.89,56,9.90,15.19 |
| 2002,3357,71540,0.11,9,50.44,22.46,81,53.38,74.94 |
| 2002,3399,72,2.61,81,4.58,3.13,31,63.52,40.03 |
| 2002,3462,88673,0.17,11,40.93,43.54,66,39.83,35.68 |
| 2002,3627,71405,0.24,21,61.60,20.18,86,30.17,2.98 |
| 2002,3732,41263,0.35,33,63.24,71.03,93,64.70,123.62 |
| 2002,3942,31747,0.28,8,14.21,22.64,29,55.38,6.42 |
| 2002,4117,54024,0.57,41,66.75,72.00,72,71.80,96.35 |
| 2002,4242,37012,0.78,47,15.86,6.80,60,97.79,170.03 |
| 2002,4302,64566,1.55,82,6.26,2.15,53,50.12,55.86 |
| 2002,4447,74056,0.48,27,16.86,11.21,56,89.56,144.94 |
| 2002,4686,76933,1.00,15,75.29,54.19,15,7.49,6.75 |
| 2002,4717,79946,0.89,57,39.12,67.27,64,7.08,1.71 |
| 2002,4723,36380,3.62,47,65.36,15.68,13,48.79,8.87 |
| 2002,4819,62327,0.77,63,44.48,52.39,82,68.44,88.28 |
| 2002,4957,36983,2.33,70,38.96,14.99,30,34.89,22.13 |
| 2002,4992,62746,0.48,15,17.23,11.02,31,60.22,97.22 |
| 2002,5004,31716,3.00,78,30.61,21.16,26,73.11,50.70 |
| 2002,5133,97233,0.58,49,59.82,43.19,84,85.44,4.07 |
| 2002,5245,45142,0.49,30,1.10,0.37,61,36.53,44.48 |
| 2002,5251,73991,3.20,96,51.64,93.16,30,27.34,8.36 |
| 2002,5449,95871,0.19,16,18.71,8.24,85,12.35,5.29 |
| 2002,5472,24589,0.24,8,34.74,26.11,33,42.48,35.38 |
| 2002,5503,34938,0.69,9,98.38,93.49,13,49.87,54.85 |
| 2002,5569,64962,0.50,50,NULL,5.68,100,93.35,78.15 |
| 2002,5583,92620,0.72,56,97.86,50.80,78,93.99,1.40 |
| 2002,5727,60671,1.14,49,43.70,1.73,43,86.92,100.16 |
| 2002,5737,39389,2.71,46,59.71,2.72,17,95.38,25.03 |
| 2002,5749,62425,1.10,100,39.91,16.04,91,88.00,54.33 |
| 2002,5841,67686,0.33,22,54.47,86.52,67,62.11,7.52 |
| 2002,5937,84826,1.59,86,97.93,75.53,54,75.03,58.06 |
| 2002,6015,91432,2.81,73,95.72,98.89,26,14.50,16.96 |
| 2002,6060,4560,0.20,15,8.54,13.22,75,62.88,117.71 |
| 2002,6139,45325,1.08,92,55.42,6.16,85,58.04,52.46 |
| 2002,6195,65834,1.33,61,88.07,88.10,46,95.23,15.42 |
| 2002,6471,70702,0.26,12,59.51,50.98,46,61.85,26.39 |
| 2002,6576,13149,1.08,13,11.27,2.64,12,63.38,111.35 |
| 2002,6609,27877,9.00,90,66.94,22.75,10,82.77,144.84 |
| 2002,6633,42874,0.93,82,31.20,35.97,88,44.86,6.40 |
| 2002,6855,15315,0.31,25,58.32,89.27,80,78.76,14.83 |
| 2002,6912,52490,1.13,85,56.88,35.84,75,40.18,0.58 |
| 2002,6931,79419,5.00,10,27.63,18.85,2,65.44,132.47 |
| 2002,7033,66414,0.26,13,94.05,1.16,50,67.17,107.46 |
| 2002,7063,21112,0.77,27,49.27,79.81,35,77.32,93.24 |
| 2002,7215,31653,0.42,32,57.07,1.72,77,92.67,171.36 |
| 2002,7561,51218,1.00,68,35.23,21.28,68,59.73,129.01 |
| 2002,7782,49798,1.04,53,83.78,112.52,51,76.63,63.44 |
| 2002,7801,88304,0.48,12,96.02,41.33,25,95.64,20.64 |
| 2002,7815,66823,0.94,72,25.50,25.58,77,53.59,76.78 |
| 2002,7957,96021,0.44,43,79.68,30.03,98,73.48,90.26 |
| 2002,8049,60811,0.39,12,81.72,20.26,31,37.82,50.91 |
| 2002,8227,91553,0.87,60,87.92,111.61,69,31.14,25.90 |
| 2002,8337,8072,0.20,12,63.82,50.02,61,98.28,5.38 |
| 2002,8349,57239,0.25,22,3.17,0.78,89,20.00,18.55 |
| 2002,8451,86351,0.42,40,83.30,109.88,96,77.69,52.56 |
| 2002,8505,27133,0.19,5,30.40,21.53,27,88.89,7.57 |
| 2002,8646,73272,0.72,23,13.59,6.28,32,17.75,13.76 |
| 2002,8761,53153,0.88,37,2.97,4.64,42,58.26,55.88 |
| 2002,8898,57830,0.57,47,30.69,17.17,83,16.40,36.64 |
| 2002,8979,50230,0.50,15,12.02,1.03,30,36.85,34.45 |
| 2002,8983,80488,0.18,7,46.94,49.14,39,51.17,61.25 |
| 2002,8989,69606,0.45,22,99.16,93.14,49,73.63,37.31 |
| 2002,9165,45023,3.67,22,32.15,14.95,6,28.47,38.94 |
| 2002,9193,88085,0.84,61,34.73,34.78,73,30.59,9.83 |
| 2002,9307,30830,0.01,1,53.62,23.51,72,74.60,55.05 |
| 2002,9327,65904,0.97,38,79.26,122.00,39,10.33,14.31 |
| 2002,9385,42467,0.52,27,64.95,23.69,52,86.42,2.37 |
| 2002,9387,87700,0.88,14,85.04,132.39,16,99.04,64.37 |
| 2002,9519,47703,1.49,85,7.64,11.98,57,83.37,144.89 |
| 2002,9541,38364,0.36,27,14.91,11.36,76,2.16,1.32 |
| 2002,9918,45262,1.09,98,3.02,1.91,90,90.79,154.11 |
| ---- TYPES |
| INT, BIGINT, INT, DOUBLE, BIGINT, DECIMAL, DECIMAL, BIGINT, DECIMAL, DECIMAL |
| ==== |
