StarRocks query performance with Apache Hudi

[atwong]

Using TPC-DS data of 1GB with Apache Hudi tables in Onehouse.ai. Ran TPC-DS query 57. Use this article to create the test environment Easiest way to load TPC-DS data into PostgreSQL | by Albert Wong | Medium.

With Apache Hudi RO tables

StarRocks > with v1 as(
    ->   select i_category, i_brand, cc_name, d_year, d_moy,
    ->         sum(cs_sales_price) sum_sales,
    ->         avg(sum(cs_sales_price)) over
    ->           (partition by i_category, i_brand,
    ->                      cc_name, d_year)
    ->           avg_monthly_sales,
    ->         rank() over
    ->           (partition by i_category, i_brand,
    ->                      cc_name
    ->            order by d_year, d_moy) rn
    ->   from public_item_ro, public_catalog_sales_ro, public_date_dim_ro, public_call_center_ro
    ->   where cs_item_sk = i_item_sk and
    ->        cs_sold_date_sk = d_date_sk and
    ->        cc_call_center_sk= cs_call_center_sk and
    ->        (
    ->          d_year = 1999 or
    ->          ( d_year = 1999-1 and d_moy =12) or
    ->          ( d_year = 1999+1 and d_moy =1)
    ->        )
    ->   group by i_category, i_brand,
    ->           cc_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.cc_name = v1_lag.cc_name and
    ->        v1.cc_name = v1_lead.cc_name and
    ->        v1.rn = v1_lag.rn + 1 and
    ->        v1.rn = v1_lead.rn - 1)
    -> select  *
    -> from v2
    -> where  d_year = 1999 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;
+------------+--------+-------+-------------------+--------------------+--------------------+--------------------+
| i_category | d_year | d_moy | avg_monthly_sales | sum_sales          | psum               | nsum               |
+------------+--------+-------+-------------------+--------------------+--------------------+--------------------+
| Men        |   1999 |     1 |         7536.1675 | 2822.2200000000003 |           15366.77 |            4568.91 |
| Music      |   1999 |     2 | 7305.858333333334 |            2838.84 | 3746.8300000000004 | 4682.7300000000005 |
| Shoes      |   1999 |     7 | 6915.220833333334 |            2512.46 |  4768.589999999999 |            8884.76 |
| Men        |   1999 |     7 |         7482.2925 | 3324.5999999999995 |            3422.34 |           11231.75 |
| Men        |   1999 |     7 | 6910.990833333333 | 2759.9500000000003 |             4606.6 |  8151.579999999999 |
| Music      |   1999 |     1 | 7960.886666666665 | 3817.5800000000004 | 14931.199999999999 |  4740.460000000001 |
| Music      |   1999 |     5 | 7857.552500000001 |            3718.35 |            4777.33 |             4717.9 |
| Children   |   1999 |     2 | 7660.096666666667 |            3548.75 |            3736.29 |            5145.36 |
| Men        |   1999 |     6 |         7482.2925 |            3422.34 |  4822.969999999999 | 3324.5999999999995 |
| Music      |   1999 |     2 | 7665.456666666666 |            3611.36 |            6319.82 |            4103.03 |
| Men        |   1999 |     2 | 7322.909166666665 | 3300.3500000000004 |  4358.889999999999 |  4926.219999999999 |
| Children   |   1999 |     4 | 6550.796666666668 | 2541.5099999999998 |               3559 | 3218.3699999999994 |
| Children   |   1999 |     3 | 7658.071666666667 |            3665.09 |            4222.77 |  4205.540000000001 |
| Music      |   1999 |     7 | 7960.886666666665 | 3989.7499999999995 |            4391.21 | 11045.380000000001 |
| Shoes      |   1999 |     5 | 6563.098333333334 | 2623.8500000000004 | 3831.0699999999997 |            4494.57 |
| Children   |   1999 |     4 | 6834.036666666667 |            2896.51 |            5840.07 |            3216.33 |
| Children   |   1999 |     1 | 7660.096666666667 |            3736.29 | 14178.720000000001 |            3548.75 |
| Women      |   1999 |     2 | 7017.650833333333 |            3098.88 | 3740.9500000000007 |            4299.15 |
| Men        |   1999 |     5 | 7493.482500000001 |            3595.76 |               4971 | 3726.7600000000007 |
| Men        |   1999 |     1 | 6797.435833333334 |            2917.39 |            12037.3 |            4138.65 |
| Shoes      |   1999 |     1 |          7379.815 |            3509.89 |           14291.37 |  4788.050000000001 |
| Music      |   1999 |     4 | 7647.739999999999 |            3781.87 | 3913.8299999999995 |            4904.37 |
| Shoes      |   1999 |     4 |          7379.815 |            3514.14 | 3657.4900000000002 |            4624.27 |
| Shoes      |   1999 |     6 | 7090.454999999999 |            3233.38 | 3598.3199999999997 | 6011.6900000000005 |
| Shoes      |   1999 |     2 | 6930.184166666667 | 3091.9300000000003 |            3755.38 |            3333.08 |
| Shoes      |   1999 |     3 |           6846.16 | 3019.1899999999996 |             4003.6 |            3592.21 |
| Children   |   1999 |     3 | 7020.350000000001 | 3201.7400000000007 | 4938.1900000000005 |            3874.13 |
| Children   |   1999 |     5 | 7660.096666666667 | 3851.8200000000006 |            5433.95 |             5087.6 |
| Women      |   1999 |     5 | 7017.650833333333 | 3212.8199999999997 |            5203.35 | 4474.0199999999995 |
| Music      |   1999 |     5 | 7960.886666666665 |            4179.19 |            4726.96 |            4391.21 |
| Shoes      |   1999 |     2 | 6563.098333333334 |            2791.95 |            4232.09 |            3822.87 |
| Men        |   1999 |     7 |         7536.1675 | 3766.6099999999997 |            4447.43 | 10746.780000000002 |
| Men        |   1999 |     6 | 7493.482500000001 | 3726.7600000000007 |            3595.76 |  4645.910000000001 |
| Children   |   1999 |     7 | 6501.399166666666 |            2743.73 | 3732.2300000000005 |  8546.300000000001 |
| Music      |   1999 |     3 | 7647.739999999999 | 3913.8299999999995 |            5398.23 |            3781.87 |
| Children   |   1999 |     2 | 6597.224999999999 | 2872.6800000000003 | 3245.5699999999997 |            3981.84 |
| Shoes      |   1999 |     2 | 6915.220833333334 |            3192.51 | 3327.9600000000005 |             4322.2 |
| Shoes      |   1999 |     3 |          7379.815 | 3657.4900000000002 |  4788.050000000001 |            3514.14 |
| Women      |   1999 |     2 | 6575.875833333334 | 2871.6099999999997 |             4816.3 |            4012.52 |
| Shoes      |   1999 |     7 | 6563.098333333334 |            2860.88 |            4494.57 |            9707.17 |
| Music      |   1999 |     2 | 6544.270833333333 | 2844.8900000000003 | 2880.0700000000006 |            4428.12 |
| Children   |   1999 |     5 | 7658.071666666667 |            3960.48 |  4205.540000000001 |            5555.67 |
| Children   |   1999 |     2 | 6349.134166666667 |            2657.28 |            3424.54 | 3589.3400000000006 |
| Music      |   1999 |     1 | 7857.552500000001 |            4170.07 |           17003.92 |            4822.07 |
| Children   |   1999 |     3 | 6636.591666666667 |            2954.35 | 3456.3100000000004 |  4807.280000000001 |
| Men        |   1999 |     7 | 7469.455000000001 |            3794.75 |             4925.2 |            7872.42 |
| Music      |   1999 |     4 | 6544.270833333333 | 2872.1200000000003 |            4428.12 | 3693.2799999999997 |
| Men        |   1999 |     4 | 6910.990833333333 | 3243.0599999999995 |  4144.889999999999 |            5533.21 |
| Music      |   1999 |     1 | 6544.270833333333 | 2880.0700000000006 | 14811.549999999996 | 2844.8900000000003 |
| Women      |   1999 |     2 | 6916.938333333334 | 3268.5399999999995 |            4739.63 | 3897.8499999999995 |
| Music      |   1999 |     3 | 7960.886666666665 | 4316.3099999999995 |  4740.460000000001 |            4726.96 |
| Men        |   1999 |     1 | 6634.776666666668 |            2991.55 | 11153.019999999999 |            3585.32 |
| Men        |   1999 |     7 | 7322.909166666665 | 3698.9800000000005 |            4327.46 |            8961.51 |
| Children   |   1999 |     5 | 6834.036666666667 |            3216.33 |            2896.51 |            3438.01 |
| Music      |   1999 |     4 | 6743.224999999999 |            3135.57 | 3994.0000000000005 |            3689.15 |
| Music      |   1999 |     1 | 7014.379166666668 |            3408.12 | 14101.769999999999 |            3877.52 |
| Shoes      |   1999 |     3 | 6930.184166666667 |            3333.08 | 3091.9300000000003 |            5277.39 |
| Children   |   1999 |     5 | 6244.496666666666 |            2648.52 |            3340.04 |            3061.51 |
| Shoes      |   1999 |     4 | 7151.230833333334 |            3559.33 |             4220.4 |            3908.65 |
| Shoes      |   1999 |     1 | 6915.220833333334 | 3327.9600000000005 |           15438.89 |            3192.51 |
| Women      |   1999 |     7 | 6575.875833333334 |            2996.45 |            3738.13 |            8393.38 |
| Music      |   1999 |     6 | 7960.886666666665 |            4391.21 |            4179.19 | 3989.7499999999995 |
| Men        |   1999 |     1 |         7482.2925 |             3913.1 |           17580.61 |            4660.72 |
| Music      |   1999 |     3 | 7665.456666666666 |            4103.03 |            3611.36 |            4931.39 |
| Music      |   1999 |     1 | 7305.858333333334 | 3746.8300000000004 | 13654.320000000002 |            2838.84 |
| Women      |   1999 |     1 | 6473.300833333334 |            2918.56 | 12318.299999999997 |            3441.14 |
| Children   |   1999 |     3 | 6244.496666666666 |             2692.4 |            3417.66 |            3340.04 |
| Shoes      |   1999 |     5 | 6915.220833333334 | 3363.5299999999997 |             4413.6 |  4768.589999999999 |
| Music      |   1999 |     3 | 7857.552500000001 |            4320.92 |            4822.07 |            4777.33 |
| Music      |   1999 |     4 | 7305.858333333334 |            3787.89 | 4682.7300000000005 |            4897.65 |
| Shoes      |   1999 |     2 | 6564.118333333333 |            3046.29 |            4298.34 |            4688.65 |
| Shoes      |   1999 |     5 | 7090.454999999999 | 3598.3199999999997 |            3885.42 |            3233.38 |
| Women      |   1999 |     7 |          6415.255 | 2931.4000000000005 |            3859.54 |  8772.650000000001 |
| Men        |   1999 |     3 | 7469.455000000001 |            3997.96 |            4454.62 |            4856.96 |
| Men        |   1999 |     4 |         7536.1675 | 4070.5299999999997 |            4616.46 |  5193.620000000001 |
| Children   |   1999 |     4 | 7658.071666666667 |  4205.540000000001 |            3665.09 |            3960.48 |
| Women      |   1999 |     6 |          6735.325 |            3289.16 | 3810.9199999999996 | 3421.5600000000004 |
| Women      |   1999 |     4 | 6473.300833333334 |            3029.66 | 4066.8400000000006 |            4452.18 |
| Women      |   1999 |     4 | 6030.885833333334 |            2590.14 |  4099.639999999999 | 3348.4100000000003 |
| Children   |   1999 |     2 | 7658.071666666667 |            4222.77 |            4822.93 |            3665.09 |
| Music      |   1999 |     6 | 7106.589166666666 | 3672.1500000000005 |  4348.829999999999 |  4135.570000000001 |
| Shoes      |   1999 |     6 |           6846.16 |             3427.3 | 4007.1100000000006 |            4791.14 |
| Shoes      |   1999 |     7 | 6930.184166666667 |            3513.24 |            4324.92 |            8995.99 |
| Women      |   1999 |     5 | 6575.875833333334 |            3159.42 | 3352.1699999999996 |            3738.13 |
| Children   |   1999 |     3 | 6501.399166666666 |            3088.82 |            4844.96 |            3211.37 |
| Shoes      |   1999 |     7 | 7151.230833333334 | 3739.2799999999997 |            4889.14 |           10018.74 |
| Children   |   1999 |     6 | 6834.036666666667 |            3438.01 |            3216.33 |            3933.33 |
| Men        |   1999 |     4 |         6092.5275 |            2705.47 |            3580.93 |            3946.78 |
| Men        |   1999 |     1 | 7469.455000000001 | 4084.3700000000003 |           15702.94 |            4454.62 |
| Shoes      |   1999 |     5 | 6930.184166666667 |            3547.09 |            5277.39 |            4324.92 |
| Music      |   1999 |     7 | 6544.270833333333 |            3169.16 | 3599.0099999999998 |            9674.19 |
| Music      |   1999 |     6 | 7665.456666666666 |            4303.32 | 4646.2300000000005 |  6216.040000000001 |
| Shoes      |   1999 |     2 | 7151.230833333334 |            3794.46 |            4267.59 |             4220.4 |
| Music      |   1999 |     6 | 7305.858333333334 |            3950.95 |            4897.65 | 4674.1900000000005 |
| Children   |   1999 |     1 | 6597.224999999999 | 3245.5699999999997 |           13023.89 | 2872.6800000000003 |
| Music      |   1999 |     3 | 7014.379166666668 | 3662.7600000000007 |            3877.52 |            4038.49 |
| Music      |   1999 |     2 | 5840.587500000001 | 2490.5099999999998 |            3128.09 | 3883.3099999999995 |
| Men        |   1999 |     2 |         6092.5275 |            2749.45 |               3614 |            3580.93 |
| Shoes      |   1999 |     6 |          7379.815 |            4039.47 |            4624.27 |            4832.28 |
| Children   |   1999 |     5 | 6550.796666666668 | 3218.3699999999994 | 2541.5099999999998 |            4617.26 |
+------------+--------+-------+-------------------+--------------------+--------------------+--------------------+
100 rows in set (7.19 sec)

StarRocks > with v1 as(
    ->   select i_category, i_brand, cc_name, d_year, d_moy,
    ->         sum(cs_sales_price) sum_sales,
    ->         avg(sum(cs_sales_price)) over
    ->           (partition by i_category, i_brand,
    ->                      cc_name, d_year)
    ->           avg_monthly_sales,
    ->         rank() over
    ->           (partition by i_category, i_brand,
    ->                      cc_name
    ->            order by d_year, d_moy) rn
    ->   from public_item_ro, public_catalog_sales_ro, public_date_dim_ro, public_call_center_ro
    ->   where cs_item_sk = i_item_sk and
    ->        cs_sold_date_sk = d_date_sk and
    ->        cc_call_center_sk= cs_call_center_sk and
    ->        (
    ->          d_year = 1999 or
    ->          ( d_year = 1999-1 and d_moy =12) or
    ->          ( d_year = 1999+1 and d_moy =1)
    ->        )
    ->   group by i_category, i_brand,
    ->           cc_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.cc_name = v1_lag.cc_name and
    ->        v1.cc_name = v1_lead.cc_name and
    ->        v1.rn = v1_lag.rn + 1 and
    ->        v1.rn = v1_lead.rn - 1)
    -> select  *
    -> from v2
    -> where  d_year = 1999 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;
+------------+--------+-------+--------------------+--------------------+--------------------+--------------------+
| i_category | d_year | d_moy | avg_monthly_sales  | sum_sales          | psum               | nsum               |
+------------+--------+-------+--------------------+--------------------+--------------------+--------------------+
| Men        |   1999 |     1 | 7536.1675000000005 | 2822.2200000000003 |           15366.77 |            4568.91 |
| Music      |   1999 |     2 |  7305.858333333334 |            2838.84 |            3746.83 | 4682.7300000000005 |
| Shoes      |   1999 |     7 |  6915.220833333333 | 2512.4600000000005 |            4768.59 |            8884.76 |
| Men        |   1999 |     7 |          7482.2925 |             3324.6 |            3422.34 | 11231.749999999996 |
| Men        |   1999 |     7 |  6910.990833333333 | 2759.9500000000003 |             4606.6 |  8151.580000000001 |
| Music      |   1999 |     1 |  7960.886666666668 |            3817.58 | 14931.199999999997 |            4740.46 |
| Music      |   1999 |     5 |  7857.552500000001 |  3718.350000000001 |            4777.33 |             4717.9 |
| Children   |   1999 |     2 |  7660.096666666667 |            3548.75 |            3736.29 |  5145.360000000001 |
| Men        |   1999 |     6 |          7482.2925 |            3422.34 |            4822.97 |             3324.6 |
| Music      |   1999 |     2 |  7665.456666666665 | 3611.3600000000006 |            6319.82 |            4103.03 |
| Men        |   1999 |     2 |  7322.909166666665 | 3300.3499999999995 |            4358.89 |            4926.22 |
| Children   |   1999 |     4 |  6550.796666666666 | 2541.5099999999998 |               3559 |            3218.37 |
| Children   |   1999 |     3 |  7658.071666666667 | 3665.0899999999997 |            4222.77 |  4205.540000000001 |
| Music      |   1999 |     7 |  7960.886666666668 | 3989.7499999999995 |            4391.21 | 11045.380000000001 |
| Shoes      |   1999 |     5 |  6563.098333333332 | 2623.8500000000004 |            3831.07 |            4494.57 |
| Children   |   1999 |     4 |  6834.036666666667 |            2896.51 |  5840.070000000001 |            3216.33 |
| Children   |   1999 |     1 |  7660.096666666667 |            3736.29 | 14178.720000000001 |            3548.75 |
| Women      |   1999 |     2 |  7017.650833333333 |            3098.88 | 3740.9500000000007 |  4299.150000000001 |
| Men        |   1999 |     5 |  7493.482500000001 |            3595.76 |               4971 |            3726.76 |
| Men        |   1999 |     1 |  6797.435833333333 |            2917.39 |            12037.3 |            4138.65 |
| Shoes      |   1999 |     1 |           7379.815 | 3509.8900000000003 |           14291.37 |            4788.05 |
| Music      |   1999 |     4 |  7647.739999999999 |            3781.87 |            3913.83 |            4904.37 |
| Shoes      |   1999 |     4 |           7379.815 | 3514.1400000000003 |            3657.49 | 4624.2699999999995 |
| Shoes      |   1999 |     6 |  7090.454999999999 |            3233.38 | 3598.3200000000006 | 6011.6900000000005 |
| Shoes      |   1999 |     2 |  6930.184166666667 | 3091.9300000000003 |            3755.38 | 3333.0799999999995 |
| Shoes      |   1999 |     3 |            6846.16 |            3019.19 | 4003.6000000000004 |            3592.21 |
| Children   |   1999 |     3 |  7020.350000000001 |            3201.74 | 4938.1900000000005 |  3874.129999999999 |
| Children   |   1999 |     5 |  7660.096666666667 |            3851.82 |            5433.95 |             5087.6 |
| Women      |   1999 |     5 |  7017.650833333333 | 3212.8199999999997 |            5203.35 |            4474.02 |
| Music      |   1999 |     5 |  7960.886666666668 | 4179.1900000000005 |  4726.960000000001 |            4391.21 |
| Shoes      |   1999 |     2 |  6563.098333333332 |            2791.95 |            4232.09 |            3822.87 |
| Men        |   1999 |     7 | 7536.1675000000005 |            3766.61 |  4447.429999999999 | 10746.780000000002 |
| Men        |   1999 |     6 |  7493.482500000001 |            3726.76 |            3595.76 |            4645.91 |
| Children   |   1999 |     7 |  6501.399166666666 | 2743.7299999999996 | 3732.2300000000005 |  8546.300000000001 |
| Music      |   1999 |     3 |  7647.739999999999 |            3913.83 |            5398.23 |            3781.87 |
| Children   |   1999 |     2 |  6597.224999999999 | 2872.6800000000003 | 3245.5699999999997 | 3981.8399999999997 |
| Shoes      |   1999 |     2 |  6915.220833333333 |            3192.51 | 3327.9599999999996 |             4322.2 |
| Shoes      |   1999 |     3 |           7379.815 |            3657.49 |            4788.05 | 3514.1400000000003 |
| Women      |   1999 |     2 |  6575.875833333334 | 2871.6099999999997 |  4816.299999999999 | 4012.5200000000004 |
| Shoes      |   1999 |     7 |  6563.098333333332 |            2860.88 |            4494.57 |            9707.17 |
| Music      |   1999 |     2 |  6544.270833333333 | 2844.8900000000003 | 2880.0699999999997 |            4428.12 |
| Children   |   1999 |     5 |  7658.071666666667 | 3960.4799999999996 |  4205.540000000001 |  5555.669999999999 |
| Children   |   1999 |     2 |  6349.134166666667 | 2657.2799999999997 | 3424.5400000000004 |            3589.34 |
| Music      |   1999 |     1 |  7857.552500000001 |            4170.07 |           17003.92 |            4822.07 |
| Children   |   1999 |     3 |  6636.591666666666 | 2954.3499999999995 |            3456.31 |            4807.28 |
| Men        |   1999 |     7 |  7469.455000000001 | 3794.7499999999995 |             4925.2 |  7872.420000000001 |
| Music      |   1999 |     4 |  6544.270833333333 | 2872.1200000000003 |            4428.12 | 3693.2799999999993 |
| Men        |   1999 |     4 |  6910.990833333333 |            3243.06 |  4144.889999999999 |  5533.209999999999 |
| Music      |   1999 |     1 |  6544.270833333333 | 2880.0699999999997 |           14811.55 | 2844.8900000000003 |
| Women      |   1999 |     2 |  6916.938333333333 | 3268.5399999999995 |            4739.63 | 3897.8500000000004 |
| Music      |   1999 |     3 |  7960.886666666668 |            4316.31 |            4740.46 |  4726.960000000001 |
| Men        |   1999 |     1 |  6634.776666666668 |            2991.55 |           11153.02 | 3585.3199999999997 |
| Men        |   1999 |     7 |  7322.909166666665 |            3698.98 |            4327.46 |  8961.509999999998 |
| Children   |   1999 |     5 |  6834.036666666667 |            3216.33 |            2896.51 |            3438.01 |
| Music      |   1999 |     4 | 6743.2249999999985 | 3135.5699999999997 | 3994.0000000000005 | 3689.1499999999996 |
| Music      |   1999 |     1 |  7014.379166666667 |            3408.12 |           14101.77 | 3877.5200000000004 |
| Shoes      |   1999 |     3 |  6930.184166666667 | 3333.0799999999995 | 3091.9300000000003 |            5277.39 |
| Children   |   1999 |     5 |  6244.496666666667 |            2648.52 | 3340.0400000000004 | 3061.5099999999993 |
| Shoes      |   1999 |     4 |  7151.230833333332 |            3559.33 |             4220.4 | 3908.6499999999996 |
| Shoes      |   1999 |     1 |  6915.220833333333 | 3327.9599999999996 |           15438.89 |            3192.51 |
| Women      |   1999 |     7 |  6575.875833333334 |            2996.45 |            3738.13 |            8393.38 |
| Music      |   1999 |     6 |  7960.886666666668 |            4391.21 | 4179.1900000000005 | 3989.7499999999995 |
| Men        |   1999 |     1 |          7482.2925 |             3913.1 |           17580.61 |            4660.72 |
| Music      |   1999 |     3 |  7665.456666666665 |            4103.03 | 3611.3600000000006 |            4931.39 |
| Music      |   1999 |     1 |  7305.858333333334 |            3746.83 | 13654.320000000002 |            2838.84 |
| Women      |   1999 |     1 |  6473.300833333334 | 2918.5599999999995 | 12318.300000000003 |            3441.14 |
| Children   |   1999 |     3 |  6244.496666666667 | 2692.3999999999996 |            3417.66 | 3340.0400000000004 |
| Shoes      |   1999 |     5 |  6915.220833333333 | 3363.5299999999997 |             4413.6 |            4768.59 |
| Music      |   1999 |     3 |  7857.552500000001 |            4320.92 |            4822.07 |            4777.33 |
| Music      |   1999 |     4 |  7305.858333333334 | 3787.8900000000003 | 4682.7300000000005 |            4897.65 |
| Shoes      |   1999 |     2 |  6564.118333333335 |            3046.29 |            4298.34 |  4688.650000000001 |
| Shoes      |   1999 |     5 |  7090.454999999999 | 3598.3200000000006 | 3885.4199999999996 |            3233.38 |
| Women      |   1999 |     7 |           6415.255 | 2931.4000000000005 |            3859.54 |            8772.65 |
| Men        |   1999 |     3 |  7469.455000000001 |            3997.96 |            4454.62 |  4856.960000000001 |
| Men        |   1999 |     4 | 7536.1675000000005 |            4070.53 |            4616.46 |            5193.62 |
| Children   |   1999 |     4 |  7658.071666666667 |  4205.540000000001 | 3665.0899999999997 | 3960.4799999999996 |
| Women      |   1999 |     6 |  6735.325000000001 |            3289.16 |            3810.92 | 3421.5599999999995 |
| Women      |   1999 |     4 |  6473.300833333334 |            3029.66 | 4066.8400000000006 |            4452.18 |
| Women      |   1999 |     4 |  6030.885833333334 |            2590.14 |            4099.64 |            3348.41 |
| Children   |   1999 |     2 |  7658.071666666667 |            4222.77 |            4822.93 | 3665.0899999999997 |
| Music      |   1999 |     6 |  7106.589166666668 | 3672.1499999999996 |            4348.83 |            4135.57 |
| Shoes      |   1999 |     6 |            6846.16 | 3427.2999999999997 | 4007.1099999999997 |            4791.14 |
| Shoes      |   1999 |     7 |  6930.184166666667 | 3513.2400000000002 |            4324.92 |            8995.99 |
| Women      |   1999 |     5 |  6575.875833333334 | 3159.4200000000005 | 3352.1699999999996 |            3738.13 |
| Children   |   1999 |     3 |  6501.399166666666 |            3088.82 |            4844.96 | 3211.3700000000003 |
| Shoes      |   1999 |     7 |  7151.230833333332 |            3739.28 |            4889.14 |           10018.74 |
| Children   |   1999 |     6 |  6834.036666666667 |            3438.01 |            3216.33 | 3933.3299999999995 |
| Men        |   1999 |     4 |          6092.5275 | 2705.4700000000003 | 3580.9300000000003 |            3946.78 |
| Men        |   1999 |     1 |  7469.455000000001 |            4084.37 | 15702.939999999999 |            4454.62 |
| Shoes      |   1999 |     5 |  6930.184166666667 |            3547.09 |            5277.39 |            4324.92 |
| Music      |   1999 |     7 |  6544.270833333333 | 3169.1600000000003 | 3599.0099999999998 |            9674.19 |
| Music      |   1999 |     6 |  7665.456666666665 |            4303.32 | 4646.2300000000005 |            6216.04 |
| Shoes      |   1999 |     2 |  7151.230833333332 |            3794.46 |            4267.59 |             4220.4 |
| Music      |   1999 |     6 |  7305.858333333334 |            3950.95 |            4897.65 | 4674.1900000000005 |
| Children   |   1999 |     1 |  6597.224999999999 | 3245.5699999999997 | 13023.890000000001 | 2872.6800000000003 |
| Music      |   1999 |     3 |  7014.379166666667 | 3662.7599999999998 | 3877.5200000000004 |            4038.49 |
| Music      |   1999 |     2 |  5840.587500000001 |            2490.51 |            3128.09 | 3883.3099999999995 |
| Men        |   1999 |     2 |          6092.5275 | 2749.4500000000003 |               3614 | 3580.9300000000003 |
| Shoes      |   1999 |     6 |           7379.815 |            4039.47 | 4624.2699999999995 |  4832.280000000001 |
| Children   |   1999 |     5 |  6550.796666666666 |            3218.37 | 2541.5099999999998 |            4617.26 |
+------------+--------+-------+--------------------+--------------------+--------------------+--------------------+
100 rows in set (4.03 sec)

[atwong]

StarRocks > with v1 as(
    ->   select i_category, i_brand, cc_name, d_year, d_moy,
    ->         sum(cs_sales_price) sum_sales,
    ->         avg(sum(cs_sales_price)) over
    ->           (partition by i_category, i_brand,
    ->                      cc_name, d_year)
    ->           avg_monthly_sales,
    ->         rank() over
    ->           (partition by i_category, i_brand,
    ->                      cc_name
    ->            order by d_year, d_moy) rn
    ->   from public_item_ro, public_catalog_sales_ro, public_date_dim_ro, public_call_center_ro
    ->   where cs_item_sk = i_item_sk and
    ->        cs_sold_date_sk = d_date_sk and
    ->        cc_call_center_sk= cs_call_center_sk and
    ->        (
    ->          d_year = 1999 or
    ->          ( d_year = 1999-1 and d_moy =12) or
    ->          ( d_year = 1999+1 and d_moy =1)
    ->        )
    ->   group by i_category, i_brand,
    ->           cc_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.cc_name = v1_lag.cc_name and
    ->        v1.cc_name = v1_lead.cc_name and
    ->        v1.rn = v1_lag.rn + 1 and
    ->        v1.rn = v1_lead.rn - 1)
    -> select  *
    -> from v2
    -> where  d_year = 1999 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;
+------------+--------+-------+--------------------+--------------------+--------------------+--------------------+
| i_category | d_year | d_moy | avg_monthly_sales  | sum_sales          | psum               | nsum               |
+------------+--------+-------+--------------------+--------------------+--------------------+--------------------+
| Men        |   1999 |     1 |          7536.1675 | 2822.2200000000003 |           15366.77 |  4568.910000000001 |
| Music      |   1999 |     2 |  7305.858333333334 |            2838.84 |            3746.83 |            4682.73 |
| Shoes      |   1999 |     7 |  6915.220833333333 |            2512.46 |            4768.59 |  8884.759999999998 |
| Men        |   1999 |     7 |          7482.2925 |             3324.6 |            3422.34 | 11231.749999999998 |
| Men        |   1999 |     7 |  6910.990833333333 | 2759.9500000000003 |             4606.6 |            8151.58 |
| Music      |   1999 |     1 |  7960.886666666666 | 3817.5799999999995 |            14931.2 |            4740.46 |
| Music      |   1999 |     5 |  7857.552500000001 |  3718.350000000001 |            4777.33 |  4717.900000000001 |
| Children   |   1999 |     2 |  7660.096666666667 | 3548.7500000000005 |            3736.29 |  5145.360000000001 |
| Men        |   1999 |     6 |          7482.2925 |            3422.34 |  4822.970000000001 |             3324.6 |
| Music      |   1999 |     2 |  7665.456666666665 | 3611.3600000000006 |            6319.82 |            4103.03 |
| Men        |   1999 |     2 |  7322.909166666667 |            3300.35 |  4358.890000000001 |            4926.22 |
| Children   |   1999 |     4 |  6550.796666666666 | 2541.5099999999998 |               3559 |            3218.37 |
| Children   |   1999 |     3 |  7658.071666666668 | 3665.0899999999997 | 4222.7699999999995 |            4205.54 |
| Music      |   1999 |     7 |  7960.886666666666 |            3989.75 |            4391.21 |           11045.38 |
| Shoes      |   1999 |     5 |  6563.098333333334 | 2623.8500000000004 |            3831.07 |            4494.57 |
| Children   |   1999 |     4 |  6834.036666666667 | 2896.5099999999998 |            5840.07 |            3216.33 |
| Children   |   1999 |     1 |  7660.096666666667 |            3736.29 | 14178.720000000001 | 3548.7500000000005 |
| Women      |   1999 |     2 |  7017.650833333333 |            3098.88 | 3740.9500000000003 |            4299.15 |
| Men        |   1999 |     5 |  7493.482500000001 | 3595.7599999999998 |  4970.999999999999 | 3726.7599999999998 |
| Men        |   1999 |     1 |  6797.435833333334 |            2917.39 |            12037.3 |  4138.650000000001 |
| Shoes      |   1999 |     1 |           7379.815 | 3509.8900000000003 | 14291.369999999999 |            4788.05 |
| Music      |   1999 |     4 |  7647.739999999999 |            3781.87 |            3913.83 |            4904.37 |
| Shoes      |   1999 |     4 |           7379.815 |            3514.14 |            3657.49 |            4624.27 |
| Shoes      |   1999 |     6 |  7090.454999999999 | 3233.3799999999997 |            3598.32 |            6011.69 |
| Shoes      |   1999 |     2 |  6930.184166666667 |            3091.93 | 3755.3799999999997 |            3333.08 |
| Shoes      |   1999 |     3 |            6846.16 |            3019.19 | 4003.6000000000004 |            3592.21 |
| Children   |   1999 |     3 |  7020.349999999999 |            3201.74 |            4938.19 |            3874.13 |
| Children   |   1999 |     5 |  7660.096666666667 | 3851.8200000000006 |            5433.95 |             5087.6 |
| Women      |   1999 |     5 |  7017.650833333333 | 3212.8199999999997 |            5203.35 | 4474.0199999999995 |
| Music      |   1999 |     5 |  7960.886666666666 | 4179.1900000000005 |            4726.96 |            4391.21 |
| Shoes      |   1999 |     2 |  6563.098333333334 |            2791.95 |            4232.09 |            3822.87 |
| Men        |   1999 |     7 |          7536.1675 |            3766.61 |            4447.43 | 10746.779999999999 |
| Men        |   1999 |     6 |  7493.482500000001 | 3726.7599999999998 | 3595.7599999999998 |            4645.91 |
| Children   |   1999 |     7 |  6501.399166666666 |            2743.73 | 3732.2300000000005 |  8546.300000000001 |
| Music      |   1999 |     3 |  7647.739999999999 |            3913.83 |            5398.23 |            3781.87 |
| Children   |   1999 |     2 |  6597.224999999999 | 2872.6800000000003 | 3245.5699999999997 | 3981.8399999999997 |
| Shoes      |   1999 |     2 |  6915.220833333333 |            3192.51 | 3327.9599999999996 |             4322.2 |
| Shoes      |   1999 |     3 |           7379.815 |            3657.49 |            4788.05 |            3514.14 |
| Women      |   1999 |     2 |  6575.875833333334 |            2871.61 |             4816.3 | 4012.5199999999995 |
| Shoes      |   1999 |     7 |  6563.098333333334 |            2860.88 |            4494.57 |            9707.17 |
| Music      |   1999 |     2 |  6544.270833333333 |            2844.89 | 2880.0699999999997 |            4428.12 |
| Children   |   1999 |     5 |  7658.071666666668 |  3960.479999999999 |            4205.54 |  5555.669999999999 |
| Children   |   1999 |     2 |  6349.134166666667 | 2657.2799999999997 | 3424.5400000000004 |            3589.34 |
| Music      |   1999 |     1 |  7857.552500000001 |            4170.07 |           17003.92 |            4822.07 |
| Children   |   1999 |     3 |  6636.591666666667 |            2954.35 | 3456.3099999999995 |            4807.28 |
| Men        |   1999 |     7 |  7469.454999999999 |            3794.75 |             4925.2 |            7872.42 |
| Music      |   1999 |     4 |  6544.270833333333 |            2872.12 |            4428.12 | 3693.2799999999993 |
| Men        |   1999 |     4 |  6910.990833333333 | 3243.0600000000004 |            4144.89 |  5533.210000000001 |
| Music      |   1999 |     1 |  6544.270833333333 | 2880.0699999999997 |           14811.55 |            2844.89 |
| Women      |   1999 |     2 |  6916.938333333333 |            3268.54 |  4739.630000000001 |            3897.85 |
| Music      |   1999 |     3 |  7960.886666666666 |            4316.31 |            4740.46 |            4726.96 |
| Men        |   1999 |     1 |  6634.776666666668 |            2991.55 | 11153.020000000002 | 3585.3199999999997 |
| Men        |   1999 |     7 |  7322.909166666667 | 3698.9799999999996 |            4327.46 |  8961.509999999998 |
| Children   |   1999 |     5 |  6834.036666666667 |            3216.33 | 2896.5099999999998 |            3438.01 |
| Music      |   1999 |     4 | 6743.2249999999985 | 3135.5699999999997 |               3994 |            3689.15 |
| Music      |   1999 |     1 |  7014.379166666668 | 3408.1200000000003 | 14101.770000000002 | 3877.5200000000004 |
| Shoes      |   1999 |     3 |  6930.184166666667 |            3333.08 |            3091.93 |  5277.389999999999 |
| Children   |   1999 |     5 |  6244.496666666667 | 2648.5199999999995 | 3340.0400000000004 | 3061.5099999999998 |
| Shoes      |   1999 |     4 |  7151.230833333334 | 3559.3299999999995 |  4220.400000000001 | 3908.6499999999996 |
| Shoes      |   1999 |     1 |  6915.220833333333 | 3327.9599999999996 |           15438.89 |            3192.51 |
| Women      |   1999 |     7 |  6575.875833333334 |            2996.45 |            3738.13 |  8393.380000000001 |
| Music      |   1999 |     6 |  7960.886666666666 |            4391.21 | 4179.1900000000005 |            3989.75 |
| Men        |   1999 |     1 |          7482.2925 |             3913.1 |           17580.61 |            4660.72 |
| Music      |   1999 |     3 |  7665.456666666665 |            4103.03 | 3611.3600000000006 |            4931.39 |
| Music      |   1999 |     1 |  7305.858333333334 |            3746.83 | 13654.320000000002 |            2838.84 |
| Women      |   1999 |     1 |  6473.300833333332 | 2918.5599999999995 | 12318.300000000001 |            3441.14 |
| Children   |   1999 |     3 |  6244.496666666667 |             2692.4 | 3417.6600000000003 | 3340.0400000000004 |
| Shoes      |   1999 |     5 |  6915.220833333333 |            3363.53 |             4413.6 |            4768.59 |
| Music      |   1999 |     3 |  7857.552500000001 |            4320.92 |            4822.07 |            4777.33 |
| Music      |   1999 |     4 |  7305.858333333334 | 3787.8900000000003 |            4682.73 |            4897.65 |
| Shoes      |   1999 |     2 |  6564.118333333332 |            3046.29 |            4298.34 |  4688.650000000001 |
| Shoes      |   1999 |     5 |  7090.454999999999 |            3598.32 | 3885.4199999999996 | 3233.3799999999997 |
| Women      |   1999 |     7 |           6415.255 |             2931.4 |            3859.54 |            8772.65 |
| Men        |   1999 |     3 |  7469.454999999999 |            3997.96 |  4454.619999999999 |  4856.960000000001 |
| Men        |   1999 |     4 |          7536.1675 | 4070.5299999999997 |            4616.46 |  5193.620000000001 |
| Children   |   1999 |     4 |  7658.071666666668 |            4205.54 | 3665.0899999999997 |  3960.479999999999 |
| Women      |   1999 |     6 |  6735.325000000001 |            3289.16 |            3810.92 | 3421.5599999999995 |
| Women      |   1999 |     4 |  6473.300833333332 |            3029.66 | 4066.8400000000006 |            4452.18 |
| Women      |   1999 |     4 |  6030.885833333333 |            2590.14 |  4099.639999999999 |            3348.41 |
| Children   |   1999 |     2 |  7658.071666666668 | 4222.7699999999995 |  4822.930000000001 | 3665.0899999999997 |
| Music      |   1999 |     6 |  7106.589166666668 |            3672.15 |            4348.83 |            4135.57 |
| Shoes      |   1999 |     6 |            6846.16 | 3427.2999999999997 |            4007.11 |  4791.139999999999 |
| Shoes      |   1999 |     7 |  6930.184166666667 | 3513.2400000000002 |            4324.92 |  8995.989999999998 |
| Women      |   1999 |     5 |  6575.875833333334 | 3159.4200000000005 |            3352.17 |            3738.13 |
| Children   |   1999 |     3 |  6501.399166666666 |            3088.82 |            4844.96 |            3211.37 |
| Shoes      |   1999 |     7 |  7151.230833333334 | 3739.2799999999997 |  4889.139999999999 |           10018.74 |
| Children   |   1999 |     6 |  6834.036666666667 |            3438.01 |            3216.33 |            3933.33 |
| Men        |   1999 |     4 |          6092.5275 | 2705.4700000000003 | 3580.9300000000003 |            3946.78 |
| Men        |   1999 |     1 |  7469.454999999999 |            4084.37 | 15702.939999999999 |  4454.619999999999 |
| Shoes      |   1999 |     5 |  6930.184166666667 |            3547.09 |  5277.389999999999 |            4324.92 |
| Music      |   1999 |     7 |  6544.270833333333 | 3169.1600000000003 | 3599.0099999999998 |            9674.19 |
| Music      |   1999 |     6 |  7665.456666666665 |            4303.32 | 4646.2300000000005 |  6216.039999999999 |
| Shoes      |   1999 |     2 |  7151.230833333334 |            3794.46 |            4267.59 |  4220.400000000001 |
| Music      |   1999 |     6 |  7305.858333333334 |            3950.95 |            4897.65 |            4674.19 |
| Children   |   1999 |     1 |  6597.224999999999 | 3245.5699999999997 | 13023.890000000003 | 2872.6800000000003 |
| Music      |   1999 |     3 |  7014.379166666668 | 3662.7599999999998 | 3877.5200000000004 |            4038.49 |
| Music      |   1999 |     2 |  5840.587500000001 | 2490.5099999999998 |            3128.09 | 3883.3099999999995 |
| Men        |   1999 |     2 |          6092.5275 | 2749.4500000000003 |               3614 | 3580.9300000000003 |
| Shoes      |   1999 |     6 |           7379.815 |            4039.47 |            4624.27 |            4832.28 |
| Children   |   1999 |     5 |  6550.796666666666 |            3218.37 | 2541.5099999999998 |            4617.26 |
+------------+--------+-------+--------------------+--------------------+--------------------+--------------------+
100 rows in set (3.08 sec)

StarRocks > with v1 as(
    ->   select i_category, i_brand, cc_name, d_year, d_moy,
    ->         sum(cs_sales_price) sum_sales,
    ->         avg(sum(cs_sales_price)) over
    ->           (partition by i_category, i_brand,
    ->                      cc_name, d_year)
    ->           avg_monthly_sales,
    ->         rank() over
    ->           (partition by i_category, i_brand,
    ->                      cc_name
    ->            order by d_year, d_moy) rn
    ->   from public_item_ro, public_catalog_sales_ro, public_date_dim_ro, public_call_center_ro
    ->   where cs_item_sk = i_item_sk and
    ->        cs_sold_date_sk = d_date_sk and
    ->        cc_call_center_sk= cs_call_center_sk and
    ->        (
    ->          d_year = 1999 or
    ->          ( d_year = 1999-1 and d_moy =12) or
    ->          ( d_year = 1999+1 and d_moy =1)
    ->        )
    ->   group by i_category, i_brand,
    ->           cc_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.cc_name = v1_lag.cc_name and
    ->        v1.cc_name = v1_lead.cc_name and
    ->        v1.rn = v1_lag.rn + 1 and
    ->        v1.rn = v1_lead.rn - 1)
    -> select  *
    -> from v2
    -> where  d_year = 1999 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;
+------------+--------+-------+--------------------+--------------------+--------------------+--------------------+
| i_category | d_year | d_moy | avg_monthly_sales  | sum_sales          | psum               | nsum               |
+------------+--------+-------+--------------------+--------------------+--------------------+--------------------+
| Men        |   1999 |     1 | 7536.1675000000005 | 2822.2200000000003 |           15366.77 |            4568.91 |
| Music      |   1999 |     2 |  7305.858333333334 |            2838.84 | 3746.8299999999995 |            4682.73 |
| Shoes      |   1999 |     7 |  6915.220833333333 |            2512.46 |            4768.59 |  8884.759999999998 |
| Men        |   1999 |     7 |          7482.2925 |             3324.6 | 3422.3399999999997 | 11231.749999999996 |
| Men        |   1999 |     7 |  6910.990833333333 | 2759.9500000000003 |  4606.599999999999 |            8151.58 |
| Music      |   1999 |     1 |  7960.886666666666 |            3817.58 |            14931.2 |            4740.46 |
| Music      |   1999 |     5 |  7857.552500000001 | 3718.3500000000004 |            4777.33 |  4717.900000000001 |
| Children   |   1999 |     2 |  7660.096666666667 |            3548.75 | 3736.2900000000004 |  5145.360000000001 |
| Men        |   1999 |     6 |          7482.2925 | 3422.3399999999997 |            4822.97 |             3324.6 |
| Music      |   1999 |     2 |  7665.456666666666 | 3611.3599999999997 |  6319.820000000001 |            4103.03 |
| Men        |   1999 |     2 |  7322.909166666665 |            3300.35 |            4358.89 |            4926.22 |
| Children   |   1999 |     4 |  6550.796666666666 | 2541.5099999999998 |               3559 |            3218.37 |
| Children   |   1999 |     3 |  7658.071666666667 |            3665.09 |            4222.77 |            4205.54 |
| Music      |   1999 |     7 |  7960.886666666666 |  3989.749999999999 |            4391.21 | 11045.380000000001 |
| Shoes      |   1999 |     5 |  6563.098333333332 | 2623.8500000000004 |            3831.07 |            4494.57 |
| Children   |   1999 |     4 |  6834.036666666668 |            2896.51 |  5840.070000000001 |            3216.33 |
| Children   |   1999 |     1 |  7660.096666666667 | 3736.2900000000004 | 14178.720000000003 |            3548.75 |
| Women      |   1999 |     2 |  7017.650833333333 |            3098.88 | 3740.9500000000007 |  4299.150000000001 |
| Men        |   1999 |     5 |  7493.482500000001 | 3595.7599999999998 |               4971 |            3726.76 |
| Men        |   1999 |     1 |  6797.435833333334 | 2917.3900000000003 |            12037.3 |  4138.650000000001 |
| Shoes      |   1999 |     1 |           7379.815 | 3509.8900000000003 |           14291.37 |            4788.05 |
| Music      |   1999 |     4 |  7647.739999999999 |            3781.87 |            3913.83 |            4904.37 |
| Shoes      |   1999 |     4 |           7379.815 | 3514.1400000000003 | 3657.4899999999993 |            4624.27 |
| Shoes      |   1999 |     6 |  7090.454999999999 |            3233.38 | 3598.3200000000006 |            6011.69 |
| Shoes      |   1999 |     2 |  6930.184166666667 | 3091.9300000000003 |            3755.38 |            3333.08 |
| Shoes      |   1999 |     3 |            6846.16 | 3019.1899999999996 | 4003.6000000000004 |            3592.21 |
| Children   |   1999 |     3 |  7020.349999999999 | 3201.7400000000002 |            4938.19 |            3874.13 |
| Children   |   1999 |     5 |  7660.096666666667 | 3851.8200000000006 |            5433.95 |             5087.6 |
| Women      |   1999 |     5 |  7017.650833333333 | 3212.8200000000006 |  5203.349999999999 |            4474.02 |
| Music      |   1999 |     5 |  7960.886666666666 | 4179.1900000000005 |  4726.960000000001 |            4391.21 |
| Shoes      |   1999 |     2 |  6563.098333333332 |            2791.95 |            4232.09 |            3822.87 |
| Men        |   1999 |     7 | 7536.1675000000005 | 3766.6100000000006 |            4447.43 |           10746.78 |
| Men        |   1999 |     6 |  7493.482500000001 |            3726.76 | 3595.7599999999998 |            4645.91 |
| Children   |   1999 |     7 |  6501.399166666666 | 2743.7299999999996 | 3732.2300000000005 |             8546.3 |
| Music      |   1999 |     3 |  7647.739999999999 |            3913.83 | 5398.2300000000005 |            3781.87 |
| Children   |   1999 |     2 |  6597.224999999999 | 2872.6800000000003 |            3245.57 |            3981.84 |
| Shoes      |   1999 |     2 |  6915.220833333333 |            3192.51 |            3327.96 |  4322.200000000001 |
| Shoes      |   1999 |     3 |           7379.815 | 3657.4899999999993 |            4788.05 | 3514.1400000000003 |
| Women      |   1999 |     2 |  6575.875833333333 | 2871.6099999999997 |             4816.3 | 4012.5199999999995 |
| Shoes      |   1999 |     7 |  6563.098333333332 | 2860.8799999999997 |            4494.57 |  9707.169999999998 |
| Music      |   1999 |     2 |  6544.270833333332 | 2844.8900000000003 | 2880.0699999999997 |  4428.120000000001 |
| Children   |   1999 |     5 |  7658.071666666667 | 3960.4799999999996 |            4205.54 |            5555.67 |
| Children   |   1999 |     2 |  6349.134166666667 | 2657.2799999999997 |            3424.54 |            3589.34 |
| Music      |   1999 |     1 |  7857.552500000001 |            4170.07 |           17003.92 |  4822.070000000001 |
| Children   |   1999 |     3 |  6636.591666666667 |            2954.35 |            3456.31 |  4807.280000000001 |
| Men        |   1999 |     7 |  7469.455000000001 |            3794.75 |             4925.2 |            7872.42 |
| Music      |   1999 |     4 |  6544.270833333332 |            2872.12 |  4428.120000000001 | 3693.2799999999993 |
| Men        |   1999 |     4 |  6910.990833333333 | 3243.0600000000004 |            4144.89 |  5533.210000000001 |
| Music      |   1999 |     1 |  6544.270833333332 | 2880.0699999999997 |           14811.55 | 2844.8900000000003 |
| Women      |   1999 |     2 |  6916.938333333333 | 3268.5399999999995 |  4739.630000000001 | 3897.8500000000004 |
| Music      |   1999 |     3 |  7960.886666666666 | 4316.3099999999995 |            4740.46 |  4726.960000000001 |
| Men        |   1999 |     1 |  6634.776666666668 |            2991.55 |           11153.02 | 3585.3199999999997 |
| Men        |   1999 |     7 |  7322.909166666665 | 3698.9800000000005 |            4327.46 |  8961.509999999998 |
| Children   |   1999 |     5 |  6834.036666666668 |            3216.33 |            2896.51 |            3438.01 |
| Music      |   1999 |     4 | 6743.2249999999985 | 3135.5699999999997 | 3993.9999999999995 |            3689.15 |
| Music      |   1999 |     1 |  7014.379166666668 | 3408.1200000000003 | 14101.770000000002 | 3877.5200000000004 |
| Shoes      |   1999 |     3 |  6930.184166666667 |            3333.08 | 3091.9300000000003 |            5277.39 |
| Children   |   1999 |     5 |  6244.496666666667 | 2648.5200000000004 | 3340.0400000000004 |            3061.51 |
| Shoes      |   1999 |     4 |  7151.230833333332 | 3559.3299999999995 |             4220.4 | 3908.6499999999996 |
| Shoes      |   1999 |     1 |  6915.220833333333 |            3327.96 |           15438.89 |            3192.51 |
| Women      |   1999 |     7 |  6575.875833333333 |            2996.45 | 3738.1300000000006 |  8393.380000000001 |
| Music      |   1999 |     6 |  7960.886666666666 |            4391.21 | 4179.1900000000005 |  3989.749999999999 |
| Men        |   1999 |     1 |          7482.2925 | 3913.1000000000004 |           17580.61 |            4660.72 |
| Music      |   1999 |     3 |  7665.456666666666 |            4103.03 | 3611.3599999999997 |            4931.39 |
| Music      |   1999 |     1 |  7305.858333333334 | 3746.8299999999995 | 13654.320000000003 |            2838.84 |
| Women      |   1999 |     1 |  6473.300833333334 |            2918.56 |            12318.3 |            3441.14 |
| Children   |   1999 |     3 |  6244.496666666667 | 2692.3999999999996 |            3417.66 | 3340.0400000000004 |
| Shoes      |   1999 |     5 |  6915.220833333333 |            3363.53 |             4413.6 |            4768.59 |
| Music      |   1999 |     3 |  7857.552500000001 |            4320.92 |  4822.070000000001 |            4777.33 |
| Music      |   1999 |     4 |  7305.858333333334 | 3787.8900000000003 |            4682.73 |            4897.65 |
| Shoes      |   1999 |     2 |  6564.118333333332 | 3046.2899999999995 |            4298.34 |  4688.650000000001 |
| Shoes      |   1999 |     5 |  7090.454999999999 | 3598.3200000000006 |            3885.42 |            3233.38 |
| Women      |   1999 |     7 |  6415.255000000001 |             2931.4 |            3859.54 |  8772.650000000001 |
| Men        |   1999 |     3 |  7469.455000000001 |            3997.96 |  4454.620000000001 |  4856.960000000001 |
| Men        |   1999 |     4 | 7536.1675000000005 |            4070.53 |            4616.46 |  5193.620000000001 |
| Children   |   1999 |     4 |  7658.071666666667 |            4205.54 |            3665.09 | 3960.4799999999996 |
| Women      |   1999 |     6 |  6735.325000000001 |            3289.16 |            3810.92 | 3421.5599999999995 |
| Women      |   1999 |     4 |  6473.300833333334 | 3029.6600000000003 |            4066.84 |            4452.18 |
| Women      |   1999 |     4 |  6030.885833333334 | 2590.1400000000003 |            4099.64 |            3348.41 |
| Children   |   1999 |     2 |  7658.071666666667 |            4222.77 |            4822.93 |            3665.09 |
| Music      |   1999 |     6 |  7106.589166666668 | 3672.1499999999996 |  4348.830000000001 |            4135.57 |
| Shoes      |   1999 |     6 |            6846.16 |             3427.3 | 4007.1100000000006 |            4791.14 |
| Shoes      |   1999 |     7 |  6930.184166666667 |            3513.24 |            4324.92 |            8995.99 |
| Women      |   1999 |     5 |  6575.875833333333 | 3159.4200000000005 | 3352.1699999999996 | 3738.1300000000006 |
| Children   |   1999 |     3 |  6501.399166666666 |            3088.82 |            4844.96 |            3211.37 |
| Shoes      |   1999 |     7 |  7151.230833333332 | 3739.2799999999997 |            4889.14 | 10018.740000000002 |
| Children   |   1999 |     6 |  6834.036666666668 |            3438.01 |            3216.33 |            3933.33 |
| Men        |   1999 |     4 |          6092.5275 | 2705.4700000000003 | 3580.9300000000003 | 3946.7800000000007 |
| Men        |   1999 |     1 |  7469.455000000001 |            4084.37 | 15702.939999999999 |  4454.620000000001 |
| Shoes      |   1999 |     5 |  6930.184166666667 |            3547.09 |            5277.39 |            4324.92 |
| Music      |   1999 |     7 |  6544.270833333332 |            3169.16 | 3599.0099999999998 |            9674.19 |
| Music      |   1999 |     6 |  7665.456666666666 |            4303.32 |            4646.23 |            6216.04 |
| Shoes      |   1999 |     2 |  7151.230833333332 |            3794.46 |            4267.59 |             4220.4 |
| Music      |   1999 |     6 |  7305.858333333334 | 3950.9500000000003 |            4897.65 | 4674.1900000000005 |
| Children   |   1999 |     1 |  6597.224999999999 |            3245.57 | 13023.890000000001 | 2872.6800000000003 |
| Music      |   1999 |     3 |  7014.379166666668 | 3662.7599999999998 | 3877.5200000000004 | 4038.4900000000002 |
| Music      |   1999 |     2 |  5840.587500000001 | 2490.5099999999998 | 3128.0899999999997 |            3883.31 |
| Men        |   1999 |     2 |          6092.5275 | 2749.4500000000003 | 3614.0000000000005 | 3580.9300000000003 |
| Shoes      |   1999 |     6 |           7379.815 | 4039.4699999999993 |            4624.27 |  4832.280000000001 |
| Children   |   1999 |     5 |  6550.796666666666 |            3218.37 | 2541.5099999999998 |            4617.26 |
+------------+--------+-------+--------------------+--------------------+--------------------+--------------------+
100 rows in set (3.04 sec)

[atwong]

With Apache Hudi RT tables

StarRocks > with v1 as(
    ->   select i_category, i_brand, cc_name, d_year, d_moy,
    ->         sum(cs_sales_price) sum_sales,
    ->         avg(sum(cs_sales_price)) over
    ->           (partition by i_category, i_brand,
    ->                      cc_name, d_year)
    ->           avg_monthly_sales,
    ->         rank() over
    ->           (partition by i_category, i_brand,
    ->                      cc_name
    ->            order by d_year, d_moy) rn
    ->   from public_item_rt, public_catalog_sales_rt, public_date_dim_rt, public_call_center_rt
    ->   where cs_item_sk = i_item_sk and
    ->        cs_sold_date_sk = d_date_sk and
    ->        cc_call_center_sk= cs_call_center_sk and
    ->        (
    ->          d_year = 1999 or
    ->          ( d_year = 1999-1 and d_moy =12) or
    ->          ( d_year = 1999+1 and d_moy =1)
    ->        )
    ->   group by i_category, i_brand,
    ->           cc_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.cc_name = v1_lag.cc_name and
    ->        v1.cc_name = v1_lead.cc_name and
    ->        v1.rn = v1_lag.rn + 1 and
    ->        v1.rn = v1_lead.rn - 1)
    -> select  *
    -> from v2
    -> where  d_year = 1999 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;
+------------+--------+-------+--------------------+--------------------+--------------------+--------------------+
| i_category | d_year | d_moy | avg_monthly_sales  | sum_sales          | psum               | nsum               |
+------------+--------+-------+--------------------+--------------------+--------------------+--------------------+
| Men        |   1999 |     1 | 7536.1675000000005 | 2822.2200000000003 | 15366.769999999999 |            4568.91 |
| Music      |   1999 |     2 |  7305.858333333334 |            2838.84 |            3746.83 | 4682.7300000000005 |
| Shoes      |   1999 |     7 |  6915.220833333333 |            2512.46 |  4768.589999999999 |  8884.759999999998 |
| Men        |   1999 |     7 |          7482.2925 |             3324.6 | 3422.3399999999997 | 11231.749999999996 |
| Men        |   1999 |     7 |  6910.990833333333 | 2759.9500000000003 |             4606.6 |            8151.58 |
| Music      |   1999 |     1 |  7960.886666666668 |            3817.58 | 14931.199999999999 |            4740.46 |
| Music      |   1999 |     5 |  7857.552500000001 | 3718.3500000000004 |            4777.33 |             4717.9 |
| Children   |   1999 |     2 |  7660.096666666667 |            3548.75 |            3736.29 |  5145.360000000001 |
| Men        |   1999 |     6 |          7482.2925 | 3422.3399999999997 |  4822.969999999999 |             3324.6 |
| Music      |   1999 |     2 |  7665.456666666666 |            3611.36 |            6319.82 |            4103.03 |
| Men        |   1999 |     2 |  7322.909166666667 |            3300.35 |  4358.890000000001 |            4926.22 |
| Children   |   1999 |     4 |  6550.796666666666 |            2541.51 | 3559.0000000000005 |            3218.37 |
| Children   |   1999 |     3 |  7658.071666666667 |            3665.09 |            4222.77 |            4205.54 |
| Music      |   1999 |     7 |  7960.886666666668 | 3989.7499999999995 |  4391.209999999999 | 11045.380000000001 |
| Shoes      |   1999 |     5 |  6563.098333333332 | 2623.8500000000004 |            3831.07 |            4494.57 |
| Children   |   1999 |     4 |  6834.036666666667 | 2896.5099999999998 |            5840.07 |            3216.33 |
| Children   |   1999 |     1 |  7660.096666666667 |            3736.29 | 14178.720000000001 |            3548.75 |
| Women      |   1999 |     2 |  7017.650833333333 |            3098.88 | 3740.9500000000007 |  4299.150000000001 |
| Men        |   1999 |     5 |  7493.482500000001 |            3595.76 |               4971 |            3726.76 |
| Men        |   1999 |     1 |  6797.435833333333 | 2917.3900000000003 | 12037.300000000001 |            4138.65 |
| Shoes      |   1999 |     1 |           7379.815 | 3509.8900000000003 | 14291.370000000003 |            4788.05 |
| Music      |   1999 |     4 |  7647.739999999999 |            3781.87 |            3913.83 |  4904.369999999999 |
| Shoes      |   1999 |     4 |           7379.815 | 3514.1400000000003 |            3657.49 |            4624.27 |
| Shoes      |   1999 |     6 |  7090.454999999999 | 3233.3799999999997 | 3598.3200000000006 |  6011.689999999999 |
| Shoes      |   1999 |     2 |  6930.184166666667 | 3091.9300000000003 | 3755.3799999999997 | 3333.0799999999995 |
| Shoes      |   1999 |     3 |            6846.16 | 3019.1899999999996 |             4003.6 |            3592.21 |
| Children   |   1999 |     3 |  7020.349999999999 |            3201.74 |            4938.19 |            3874.13 |
| Children   |   1999 |     5 |  7660.096666666667 |            3851.82 |  5433.949999999999 |             5087.6 |
| Women      |   1999 |     5 |  7017.650833333333 | 3212.8199999999997 |            5203.35 | 4474.0199999999995 |
| Music      |   1999 |     5 |  7960.886666666668 | 4179.1900000000005 |  4726.960000000001 |  4391.209999999999 |
| Shoes      |   1999 |     2 |  6563.098333333332 |            2791.95 |            4232.09 |            3822.87 |
| Men        |   1999 |     7 | 7536.1675000000005 | 3766.6099999999997 |            4447.43 |           10746.78 |
| Men        |   1999 |     6 |  7493.482500000001 |            3726.76 |            3595.76 |            4645.91 |
| Children   |   1999 |     7 |  6501.399166666666 |            2743.73 |            3732.23 |  8546.300000000001 |
| Music      |   1999 |     3 |  7647.739999999999 |            3913.83 | 5398.2300000000005 |            3781.87 |
| Children   |   1999 |     2 |  6597.224999999999 | 2872.6800000000003 | 3245.5700000000006 |            3981.84 |
| Shoes      |   1999 |     2 |  6915.220833333333 |            3192.51 |            3327.96 |             4322.2 |
| Shoes      |   1999 |     3 |           7379.815 |            3657.49 |            4788.05 | 3514.1400000000003 |
| Women      |   1999 |     2 |  6575.875833333333 |            2871.61 |             4816.3 | 4012.5200000000004 |
| Shoes      |   1999 |     7 |  6563.098333333332 |            2860.88 |            4494.57 |  9707.169999999998 |
| Music      |   1999 |     2 |  6544.270833333333 | 2844.8900000000003 | 2880.0699999999997 |            4428.12 |
| Children   |   1999 |     5 |  7658.071666666667 |            3960.48 |            4205.54 |            5555.67 |
| Children   |   1999 |     2 |  6349.134166666667 | 2657.2799999999997 |  3424.540000000001 |            3589.34 |
| Music      |   1999 |     1 |  7857.552500000001 |            4170.07 |           17003.92 |            4822.07 |
| Children   |   1999 |     3 |  6636.591666666667 | 2954.3500000000004 |            3456.31 |  4807.280000000001 |
| Men        |   1999 |     7 |  7469.455000000001 |            3794.75 |             4925.2 |  7872.419999999999 |
| Music      |   1999 |     4 |  6544.270833333333 |            2872.12 |            4428.12 | 3693.2799999999997 |
| Men        |   1999 |     4 |  6910.990833333333 |            3243.06 |            4144.89 |            5533.21 |
| Music      |   1999 |     1 |  6544.270833333333 | 2880.0699999999997 | 14811.550000000001 | 2844.8900000000003 |
| Women      |   1999 |     2 |  6916.938333333333 |            3268.54 |  4739.630000000001 |            3897.85 |
| Music      |   1999 |     3 |  7960.886666666668 |            4316.31 |            4740.46 |  4726.960000000001 |
| Men        |   1999 |     1 |  6634.776666666666 |            2991.55 |           11153.02 | 3585.3199999999997 |
| Men        |   1999 |     7 |  7322.909166666667 | 3698.9800000000005 |            4327.46 |  8961.509999999998 |
| Children   |   1999 |     5 |  6834.036666666667 |            3216.33 | 2896.5099999999998 | 3438.0100000000007 |
| Music      |   1999 |     4 |  6743.225000000001 | 3135.5699999999997 |               3994 | 3689.1499999999996 |
| Music      |   1999 |     1 |  7014.379166666667 | 3408.1200000000003 |           14101.77 | 3877.5200000000004 |
| Shoes      |   1999 |     3 |  6930.184166666667 | 3333.0799999999995 | 3091.9300000000003 |  5277.389999999999 |
| Children   |   1999 |     5 |  6244.496666666667 |            2648.52 |            3340.04 |            3061.51 |
| Shoes      |   1999 |     4 |  7151.230833333334 |            3559.33 |             4220.4 | 3908.6499999999996 |
| Shoes      |   1999 |     1 |  6915.220833333333 |            3327.96 |           15438.89 |            3192.51 |
| Women      |   1999 |     7 |  6575.875833333333 |            2996.45 | 3738.1300000000006 |            8393.38 |
| Music      |   1999 |     6 |  7960.886666666668 |  4391.209999999999 | 4179.1900000000005 | 3989.7499999999995 |
| Men        |   1999 |     1 |          7482.2925 |             3913.1 | 17580.610000000004 |            4660.72 |
| Music      |   1999 |     3 |  7665.456666666666 |            4103.03 |            3611.36 |  4931.390000000001 |
| Music      |   1999 |     1 |  7305.858333333334 |            3746.83 | 13654.320000000002 |            2838.84 |
| Women      |   1999 |     1 |  6473.300833333334 | 2918.5600000000004 | 12318.300000000001 | 3441.1400000000003 |
| Children   |   1999 |     3 |  6244.496666666667 |             2692.4 |            3417.66 |            3340.04 |
| Shoes      |   1999 |     5 |  6915.220833333333 |            3363.53 |  4413.600000000001 |  4768.589999999999 |
| Music      |   1999 |     3 |  7857.552500000001 |            4320.92 |            4822.07 |            4777.33 |
| Music      |   1999 |     4 |  7305.858333333334 | 3787.8900000000003 | 4682.7300000000005 |            4897.65 |
| Shoes      |   1999 |     2 |  6564.118333333332 |            3046.29 |            4298.34 |            4688.65 |
| Shoes      |   1999 |     5 |  7090.454999999999 | 3598.3200000000006 |            3885.42 | 3233.3799999999997 |
| Women      |   1999 |     7 |           6415.255 |             2931.4 |            3859.54 |            8772.65 |
| Men        |   1999 |     3 |  7469.455000000001 |            3997.96 |            4454.62 |            4856.96 |
| Men        |   1999 |     4 | 7536.1675000000005 | 4070.5300000000007 |            4616.46 |            5193.62 |
| Children   |   1999 |     4 |  7658.071666666667 |            4205.54 |            3665.09 |            3960.48 |
| Women      |   1999 |     6 |  6735.325000000001 |            3289.16 |            3810.92 | 3421.5599999999995 |
| Women      |   1999 |     4 |  6473.300833333334 | 3029.6600000000003 |            4066.84 |            4452.18 |
| Women      |   1999 |     4 |  6030.885833333334 | 2590.1400000000003 |            4099.64 |            3348.41 |
| Children   |   1999 |     2 |  7658.071666666667 |            4222.77 |            4822.93 |            3665.09 |
| Music      |   1999 |     6 |  7106.589166666668 |            3672.15 |            4348.83 |            4135.57 |
| Shoes      |   1999 |     6 |            6846.16 |             3427.3 | 4007.1099999999997 |  4791.139999999999 |
| Shoes      |   1999 |     7 |  6930.184166666667 |            3513.24 |            4324.92 |  8995.989999999998 |
| Women      |   1999 |     5 |  6575.875833333333 |            3159.42 |            3352.17 | 3738.1300000000006 |
| Children   |   1999 |     3 |  6501.399166666666 |            3088.82 |  4844.960000000001 |            3211.37 |
| Shoes      |   1999 |     7 |  7151.230833333334 |            3739.28 |            4889.14 |           10018.74 |
| Children   |   1999 |     6 |  6834.036666666667 | 3438.0100000000007 |            3216.33 |            3933.33 |
| Men        |   1999 |     4 |          6092.5275 | 2705.4700000000003 | 3580.9300000000003 | 3946.7799999999997 |
| Men        |   1999 |     1 |  7469.455000000001 |            4084.37 | 15702.939999999997 |            4454.62 |
| Shoes      |   1999 |     5 |  6930.184166666667 |            3547.09 |  5277.389999999999 |            4324.92 |
| Music      |   1999 |     7 |  6544.270833333333 |            3169.16 | 3599.0099999999998 |  9674.189999999999 |
| Music      |   1999 |     6 |  7665.456666666666 |            4303.32 | 4646.2300000000005 |            6216.04 |
| Shoes      |   1999 |     2 |  7151.230833333334 |            3794.46 |  4267.589999999999 |             4220.4 |
| Music      |   1999 |     6 |  7305.858333333334 | 3950.9500000000003 |            4897.65 |            4674.19 |
| Children   |   1999 |     1 |  6597.224999999999 | 3245.5700000000006 | 13023.890000000001 | 2872.6800000000003 |
| Music      |   1999 |     3 |  7014.379166666667 | 3662.7599999999993 | 3877.5200000000004 | 4038.4900000000002 |
| Music      |   1999 |     2 |  5840.587500000001 |            2490.51 | 3128.0899999999997 | 3883.3099999999995 |
| Men        |   1999 |     2 |          6092.5275 | 2749.4500000000003 |               3614 | 3580.9300000000003 |
| Shoes      |   1999 |     6 |           7379.815 | 4039.4700000000003 |            4624.27 |  4832.280000000001 |
| Children   |   1999 |     5 |  6550.796666666666 |            3218.37 |            2541.51 |            4617.26 |
+------------+--------+-------+--------------------+--------------------+--------------------+--------------------+
100 rows in set (7.22 sec)

StarRocks > with v1 as(
    ->   select i_category, i_brand, cc_name, d_year, d_moy,
    ->         sum(cs_sales_price) sum_sales,
    ->         avg(sum(cs_sales_price)) over
    ->           (partition by i_category, i_brand,
    ->                      cc_name, d_year)
    ->           avg_monthly_sales,
    ->         rank() over
    ->           (partition by i_category, i_brand,
    ->                      cc_name
    ->            order by d_year, d_moy) rn
    ->   from public_item_rt, public_catalog_sales_rt, public_date_dim_rt, public_call_center_rt
    ->   where cs_item_sk = i_item_sk and
    ->        cs_sold_date_sk = d_date_sk and
    ->        cc_call_center_sk= cs_call_center_sk and
    ->        (
    ->          d_year = 1999 or
    ->          ( d_year = 1999-1 and d_moy =12) or
    ->          ( d_year = 1999+1 and d_moy =1)
    ->        )
    ->   group by i_category, i_brand,
    ->           cc_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.cc_name = v1_lag.cc_name and
    ->        v1.cc_name = v1_lead.cc_name and
    ->        v1.rn = v1_lag.rn + 1 and
    ->        v1.rn = v1_lead.rn - 1)
    -> select  *
    -> from v2
    -> where  d_year = 1999 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;
+------------+--------+-------+--------------------+--------------------+--------------------+--------------------+
| i_category | d_year | d_moy | avg_monthly_sales  | sum_sales          | psum               | nsum               |
+------------+--------+-------+--------------------+--------------------+--------------------+--------------------+
| Men        |   1999 |     1 |          7536.1675 | 2822.2200000000003 |           15366.77 |  4568.910000000001 |
| Music      |   1999 |     2 |  7305.858333333334 |            2838.84 |            3746.83 |            4682.73 |
| Shoes      |   1999 |     7 |  6915.220833333333 | 2512.4600000000005 |            4768.59 |  8884.759999999998 |
| Men        |   1999 |     7 |          7482.2925 | 3324.5999999999995 |            3422.34 | 11231.749999999996 |
| Men        |   1999 |     7 |  6910.990833333333 | 2759.9500000000003 |  4606.599999999999 |  8151.580000000001 |
| Music      |   1999 |     1 |  7960.886666666668 |            3817.58 | 14931.199999999999 |            4740.46 |
| Music      |   1999 |     5 |  7857.552500000001 | 3718.3500000000004 |            4777.33 |  4717.900000000001 |
| Children   |   1999 |     2 |  7660.096666666667 |            3548.75 |            3736.29 |  5145.360000000001 |
| Men        |   1999 |     6 |          7482.2925 |            3422.34 |            4822.97 | 3324.5999999999995 |
| Music      |   1999 |     2 |  7665.456666666666 |            3611.36 |  6319.820000000001 |            4103.03 |
| Men        |   1999 |     2 |  7322.909166666667 |            3300.35 |  4358.890000000001 |            4926.22 |
| Children   |   1999 |     4 |  6550.796666666666 |            2541.51 |               3559 |            3218.37 |
| Children   |   1999 |     3 |  7658.071666666667 |            3665.09 | 4222.7699999999995 |            4205.54 |
| Music      |   1999 |     7 |  7960.886666666668 |  3989.749999999999 |  4391.209999999999 |           11045.38 |
| Shoes      |   1999 |     5 |  6563.098333333332 | 2623.8500000000004 |            3831.07 |            4494.57 |
| Children   |   1999 |     4 |  6834.036666666667 | 2896.5099999999998 |            5840.07 | 3216.3300000000004 |
| Children   |   1999 |     1 |  7660.096666666667 |            3736.29 | 14178.720000000001 |            3548.75 |
| Women      |   1999 |     2 |  7017.650833333333 |            3098.88 | 3740.9500000000003 |  4299.150000000001 |
| Men        |   1999 |     5 |  7493.482500000001 | 3595.7599999999998 |               4971 | 3726.7599999999993 |
| Men        |   1999 |     1 |  6797.435833333333 |            2917.39 |            12037.3 |  4138.650000000001 |
| Shoes      |   1999 |     1 |           7379.815 |            3509.89 |           14291.37 |  4788.049999999999 |
| Music      |   1999 |     4 |  7647.739999999999 | 3781.8700000000003 |            3913.83 |  4904.370000000001 |
| Shoes      |   1999 |     4 |           7379.815 | 3514.1399999999994 |            3657.49 |            4624.27 |
| Shoes      |   1999 |     6 |  7090.454999999999 |            3233.38 |            3598.32 |            6011.69 |
| Shoes      |   1999 |     2 |  6930.184166666667 | 3091.9300000000003 | 3755.3799999999997 |            3333.08 |
| Shoes      |   1999 |     3 |            6846.16 | 3019.1899999999996 | 4003.6000000000004 |            3592.21 |
| Children   |   1999 |     3 |  7020.349999999999 | 3201.7399999999993 |            4938.19 |            3874.13 |
| Children   |   1999 |     5 |  7660.096666666667 |            3851.82 |            5433.95 |             5087.6 |
| Women      |   1999 |     5 |  7017.650833333333 |            3212.82 |            5203.35 |            4474.02 |
| Music      |   1999 |     5 |  7960.886666666668 | 4179.1900000000005 |  4726.960000000001 |  4391.209999999999 |
| Shoes      |   1999 |     2 |  6563.098333333332 |            2791.95 |            4232.09 |            3822.87 |
| Men        |   1999 |     7 |          7536.1675 |            3766.61 |            4447.43 | 10746.779999999999 |
| Men        |   1999 |     6 |  7493.482500000001 | 3726.7599999999993 | 3595.7599999999998 |            4645.91 |
| Children   |   1999 |     7 |  6501.399166666667 |            2743.73 | 3732.2300000000005 |  8546.300000000001 |
| Music      |   1999 |     3 |  7647.739999999999 |            3913.83 | 5398.2300000000005 | 3781.8700000000003 |
| Children   |   1999 |     2 |  6597.224999999999 |            2872.68 | 3245.5700000000006 | 3981.8399999999997 |
| Shoes      |   1999 |     2 |  6915.220833333333 |            3192.51 | 3327.9599999999996 |             4322.2 |
| Shoes      |   1999 |     3 |           7379.815 |            3657.49 |  4788.049999999999 | 3514.1399999999994 |
| Women      |   1999 |     2 |  6575.875833333333 |            2871.61 |             4816.3 | 4012.5200000000004 |
| Shoes      |   1999 |     7 |  6563.098333333332 |            2860.88 |            4494.57 |  9707.169999999998 |
| Music      |   1999 |     2 |  6544.270833333332 | 2844.8900000000003 | 2880.0699999999997 |            4428.12 |
| Children   |   1999 |     5 |  7658.071666666667 |            3960.48 |            4205.54 |  5555.670000000001 |
| Children   |   1999 |     2 |  6349.134166666667 | 2657.2799999999997 |  3424.540000000001 |            3589.34 |
| Music      |   1999 |     1 |  7857.552500000001 |            4170.07 |           17003.92 |            4822.07 |
| Children   |   1999 |     3 |  6636.591666666667 | 2954.3500000000004 | 3456.3099999999995 |            4807.28 |
| Men        |   1999 |     7 |  7469.455000000001 |            3794.75 |             4925.2 |            7872.42 |
| Music      |   1999 |     4 |  6544.270833333332 |            2872.12 |            4428.12 | 3693.2799999999993 |
| Men        |   1999 |     4 |  6910.990833333333 | 3243.0599999999995 |  4144.889999999999 |            5533.21 |
| Music      |   1999 |     1 |  6544.270833333332 | 2880.0699999999997 |           14811.55 | 2844.8900000000003 |
| Women      |   1999 |     2 |  6916.938333333333 |            3268.54 |            4739.63 | 3897.8500000000004 |
| Music      |   1999 |     3 |  7960.886666666668 |            4316.31 |            4740.46 |  4726.960000000001 |
| Men        |   1999 |     1 |  6634.776666666668 | 2991.5499999999997 | 11153.020000000002 | 3585.3199999999997 |
| Men        |   1999 |     7 |  7322.909166666667 | 3698.9800000000005 |            4327.46 |            8961.51 |
| Children   |   1999 |     5 |  6834.036666666667 | 3216.3300000000004 | 2896.5099999999998 |            3438.01 |
| Music      |   1999 |     4 | 6743.2249999999985 |            3135.57 |               3994 | 3689.1499999999996 |
| Music      |   1999 |     1 |  7014.379166666667 | 3408.1200000000003 |           14101.77 |            3877.52 |
| Shoes      |   1999 |     3 |  6930.184166666667 |            3333.08 | 3091.9300000000003 |            5277.39 |
| Children   |   1999 |     5 |  6244.496666666667 |            2648.52 | 3340.0400000000004 | 3061.5099999999998 |
| Shoes      |   1999 |     4 |  7151.230833333332 | 3559.3299999999995 |  4220.400000000001 | 3908.6499999999996 |
| Shoes      |   1999 |     1 |  6915.220833333333 | 3327.9599999999996 |           15438.89 |            3192.51 |
| Women      |   1999 |     7 |  6575.875833333333 |            2996.45 |            3738.13 |  8393.380000000001 |
| Music      |   1999 |     6 |  7960.886666666668 |  4391.209999999999 | 4179.1900000000005 |  3989.749999999999 |
| Men        |   1999 |     1 |          7482.2925 |             3913.1 |           17580.61 |            4660.72 |
| Music      |   1999 |     3 |  7665.456666666666 |            4103.03 |            3611.36 |            4931.39 |
| Music      |   1999 |     1 |  7305.858333333334 |            3746.83 | 13654.320000000002 |            2838.84 |
| Women      |   1999 |     1 |  6473.300833333332 | 2918.5599999999995 | 12318.300000000003 | 3441.1399999999994 |
| Children   |   1999 |     3 |  6244.496666666667 | 2692.3999999999996 | 3417.6600000000003 | 3340.0400000000004 |
| Shoes      |   1999 |     5 |  6915.220833333333 | 3363.5299999999997 |             4413.6 |            4768.59 |
| Music      |   1999 |     3 |  7857.552500000001 |            4320.92 |            4822.07 |            4777.33 |
| Music      |   1999 |     4 |  7305.858333333334 |  3787.890000000001 |            4682.73 |            4897.65 |
| Shoes      |   1999 |     2 |  6564.118333333335 |            3046.29 |            4298.34 |            4688.65 |
| Shoes      |   1999 |     5 |  7090.454999999999 |            3598.32 |            3885.42 |            3233.38 |
| Women      |   1999 |     7 |           6415.255 |             2931.4 | 3859.5399999999995 |            8772.65 |
| Men        |   1999 |     3 |  7469.455000000001 |            3997.96 |            4454.62 |  4856.960000000001 |
| Men        |   1999 |     4 |          7536.1675 |            4070.53 |            4616.46 |            5193.62 |
| Children   |   1999 |     4 |  7658.071666666667 |            4205.54 |            3665.09 |            3960.48 |
| Women      |   1999 |     6 |  6735.325000000001 |            3289.16 | 3810.9200000000005 | 3421.5599999999995 |
| Women      |   1999 |     4 |  6473.300833333332 |            3029.66 | 4066.8399999999997 |            4452.18 |
| Women      |   1999 |     4 |  6030.885833333334 | 2590.1400000000003 |  4099.639999999999 | 3348.4100000000003 |
| Children   |   1999 |     2 |  7658.071666666667 | 4222.7699999999995 |            4822.93 |            3665.09 |
| Music      |   1999 |     6 |  7106.589166666668 |            3672.15 |            4348.83 |            4135.57 |
| Shoes      |   1999 |     6 |            6846.16 |             3427.3 | 4007.1100000000006 |  4791.139999999999 |
| Shoes      |   1999 |     7 |  6930.184166666667 | 3513.2400000000002 |            4324.92 |            8995.99 |
| Women      |   1999 |     5 |  6575.875833333333 |            3159.42 |            3352.17 |            3738.13 |
| Children   |   1999 |     3 |  6501.399166666667 |            3088.82 |            4844.96 |            3211.37 |
| Shoes      |   1999 |     7 |  7151.230833333332 |            3739.28 |  4889.139999999999 | 10018.740000000002 |
| Children   |   1999 |     6 |  6834.036666666667 |            3438.01 | 3216.3300000000004 | 3933.3299999999995 |
| Men        |   1999 |     4 |          6092.5275 | 2705.4700000000007 |            3580.93 | 3946.7799999999997 |
| Men        |   1999 |     1 |  7469.455000000001 |            4084.37 | 15702.939999999999 |            4454.62 |
| Shoes      |   1999 |     5 |  6930.184166666667 |            3547.09 |            5277.39 |            4324.92 |
| Music      |   1999 |     7 |  6544.270833333332 |            3169.16 | 3599.0099999999998 |            9674.19 |
| Music      |   1999 |     6 |  7665.456666666666 |            4303.32 |            4646.23 |            6216.04 |
| Shoes      |   1999 |     2 |  7151.230833333332 | 3794.4600000000005 |            4267.59 |  4220.400000000001 |
| Music      |   1999 |     6 |  7305.858333333334 | 3950.9499999999994 |            4897.65 |            4674.19 |
| Children   |   1999 |     1 |  6597.224999999999 | 3245.5700000000006 | 13023.890000000001 |            2872.68 |
| Music      |   1999 |     3 |  7014.379166666667 |            3662.76 |            3877.52 |            4038.49 |
| Music      |   1999 |     2 |  5840.587500000001 |            2490.51 |            3128.09 | 3883.3099999999995 |
| Men        |   1999 |     2 |          6092.5275 | 2749.4500000000003 |               3614 |            3580.93 |
| Shoes      |   1999 |     6 |           7379.815 |            4039.47 |            4624.27 |  4832.280000000001 |
| Children   |   1999 |     5 |  6550.796666666666 |            3218.37 |            2541.51 |            4617.26 |
+------------+--------+-------+--------------------+--------------------+--------------------+--------------------+
100 rows in set (3.06 sec)

[atwong]

StarRocks > with v1 as(
    ->   select i_category, i_brand, cc_name, d_year, d_moy,
    ->         sum(cs_sales_price) sum_sales,
    ->         avg(sum(cs_sales_price)) over
    ->           (partition by i_category, i_brand,
    ->                      cc_name, d_year)
    ->           avg_monthly_sales,
    ->         rank() over
    ->           (partition by i_category, i_brand,
    ->                      cc_name
    ->            order by d_year, d_moy) rn
    ->   from public_item_rt, public_catalog_sales_rt, public_date_dim_rt, public_call_center_rt
    ->   where cs_item_sk = i_item_sk and
    ->        cs_sold_date_sk = d_date_sk and
    ->        cc_call_center_sk= cs_call_center_sk and
    ->        (
    ->          d_year = 1999 or
    ->          ( d_year = 1999-1 and d_moy =12) or
    ->          ( d_year = 1999+1 and d_moy =1)
    ->        )
    ->   group by i_category, i_brand,
    ->           cc_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.cc_name = v1_lag.cc_name and
    ->        v1.cc_name = v1_lead.cc_name and
    ->        v1.rn = v1_lag.rn + 1 and
    ->        v1.rn = v1_lead.rn - 1)
    -> select  *
    -> from v2
    -> where  d_year = 1999 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;
+------------+--------+-------+--------------------+--------------------+--------------------+--------------------+
| i_category | d_year | d_moy | avg_monthly_sales  | sum_sales          | psum               | nsum               |
+------------+--------+-------+--------------------+--------------------+--------------------+--------------------+
| Men        |   1999 |     1 |          7536.1675 | 2822.2200000000003 |           15366.77 |            4568.91 |
| Music      |   1999 |     2 |  7305.858333333334 | 2838.8399999999997 |            3746.83 |            4682.73 |
| Shoes      |   1999 |     7 |  6915.220833333333 |            2512.46 |            4768.59 |            8884.76 |
| Men        |   1999 |     7 |          7482.2925 |             3324.6 | 3422.3399999999997 | 11231.749999999996 |
| Men        |   1999 |     7 |  6910.990833333333 | 2759.9500000000003 |             4606.6 |            8151.58 |
| Music      |   1999 |     1 |  7960.886666666666 |            3817.58 | 14931.199999999997 |  4740.459999999999 |
| Music      |   1999 |     5 |  7857.552499999999 |  3718.350000000001 |            4777.33 |             4717.9 |
| Children   |   1999 |     2 |  7660.096666666667 |            3548.75 |            3736.29 |  5145.360000000001 |
| Men        |   1999 |     6 |          7482.2925 | 3422.3399999999997 |            4822.97 |             3324.6 |
| Music      |   1999 |     2 |  7665.456666666665 | 3611.3599999999997 |  6319.820000000001 |            4103.03 |
| Men        |   1999 |     2 |  7322.909166666667 |            3300.35 |  4358.890000000001 |            4926.22 |
| Children   |   1999 |     4 |  6550.796666666666 | 2541.5099999999993 | 3559.0000000000005 |            3218.37 |
| Children   |   1999 |     3 |  7658.071666666667 |            3665.09 |            4222.77 |            4205.54 |
| Music      |   1999 |     7 |  7960.886666666666 |            3989.75 |  4391.209999999999 | 11045.380000000001 |
| Shoes      |   1999 |     5 |  6563.098333333332 |            2623.85 | 3831.0700000000006 |            4494.57 |
| Children   |   1999 |     4 |  6834.036666666667 | 2896.5099999999998 |  5840.070000000001 |            3216.33 |
| Children   |   1999 |     1 |  7660.096666666667 |            3736.29 | 14178.720000000001 |            3548.75 |
| Women      |   1999 |     2 |  7017.650833333333 | 3098.8800000000006 | 3740.9500000000003 |            4299.15 |
| Men        |   1999 |     5 |  7493.482500000001 | 3595.7599999999998 |  4970.999999999999 | 3726.7599999999998 |
| Men        |   1999 |     1 |  6797.435833333333 |            2917.39 | 12037.300000000001 |            4138.65 |
| Shoes      |   1999 |     1 |           7379.815 | 3509.8900000000003 | 14291.370000000003 |  4788.049999999999 |
| Music      |   1999 |     4 |  7647.739999999999 |            3781.87 | 3913.8300000000004 |            4904.37 |
| Shoes      |   1999 |     4 |           7379.815 |            3514.14 | 3657.4900000000002 |            4624.27 |
| Shoes      |   1999 |     6 |  7090.455000000001 |            3233.38 | 3598.3200000000006 |            6011.69 |
| Shoes      |   1999 |     2 |  6930.184166666667 |            3091.93 |  3755.379999999999 |            3333.08 |
| Shoes      |   1999 |     3 |            6846.16 | 3019.1899999999996 | 4003.6000000000004 | 3592.2100000000005 |
| Children   |   1999 |     3 |  7020.349999999999 |            3201.74 |            4938.19 | 3874.1300000000006 |
| Children   |   1999 |     5 |  7660.096666666667 |            3851.82 |  5433.949999999999 |             5087.6 |
| Women      |   1999 |     5 |  7017.650833333333 | 3212.8199999999997 |            5203.35 | 4474.0199999999995 |
| Music      |   1999 |     5 |  7960.886666666666 | 4179.1900000000005 |  4726.960000000001 |  4391.209999999999 |
| Shoes      |   1999 |     2 |  6563.098333333332 | 2791.9500000000003 |            4232.09 |            3822.87 |
| Men        |   1999 |     7 |          7536.1675 | 3766.6099999999997 |            4447.43 | 10746.779999999999 |
| Men        |   1999 |     6 |  7493.482500000001 | 3726.7599999999998 | 3595.7599999999998 |            4645.91 |
| Children   |   1999 |     7 |  6501.399166666666 |            2743.73 | 3732.2300000000005 |  8546.300000000001 |
| Music      |   1999 |     3 |  7647.739999999999 | 3913.8300000000004 |            5398.23 |            3781.87 |
| Children   |   1999 |     2 |  6597.224999999999 |            2872.68 | 3245.5699999999997 |            3981.84 |
| Shoes      |   1999 |     2 |  6915.220833333333 | 3192.5099999999998 | 3327.9599999999996 |             4322.2 |
| Shoes      |   1999 |     3 |           7379.815 | 3657.4900000000002 |  4788.049999999999 |            3514.14 |
| Women      |   1999 |     2 |  6575.875833333334 |            2871.61 |             4816.3 | 4012.5200000000004 |
| Shoes      |   1999 |     7 |  6563.098333333332 |            2860.88 |            4494.57 |            9707.17 |
| Music      |   1999 |     2 |  6544.270833333332 | 2844.8900000000003 | 2880.0699999999997 |            4428.12 |
| Children   |   1999 |     5 |  7658.071666666667 | 3960.4799999999996 |            4205.54 |            5555.67 |
| Children   |   1999 |     2 |  6349.134166666667 | 2657.2799999999997 |  3424.540000000001 |            3589.34 |
| Music      |   1999 |     1 |  7857.552499999999 |            4170.07 |           17003.92 |            4822.07 |
| Children   |   1999 |     3 |  6636.591666666666 | 2954.3499999999995 |            3456.31 |  4807.280000000001 |
| Men        |   1999 |     7 |  7469.455000000001 |            3794.75 |             4925.2 |  7872.419999999999 |
| Music      |   1999 |     4 |  6544.270833333332 | 2872.1200000000003 |            4428.12 | 3693.2799999999997 |
| Men        |   1999 |     4 |  6910.990833333333 |            3243.06 |            4144.89 |            5533.21 |
| Music      |   1999 |     1 |  6544.270833333332 | 2880.0699999999997 | 14811.549999999997 | 2844.8900000000003 |
| Women      |   1999 |     2 |  6916.938333333333 |            3268.54 |            4739.63 | 3897.8500000000004 |
| Music      |   1999 |     3 |  7960.886666666666 | 4316.3099999999995 |  4740.459999999999 |  4726.960000000001 |
| Men        |   1999 |     1 |  6634.776666666668 | 2991.5499999999997 | 11153.020000000002 | 3585.3199999999997 |
| Men        |   1999 |     7 |  7322.909166666667 | 3698.9800000000005 |  4327.460000000001 |  8961.509999999998 |
| Children   |   1999 |     5 |  6834.036666666667 |            3216.33 | 2896.5099999999998 |            3438.01 |
| Music      |   1999 |     4 | 6743.2249999999985 |            3135.57 | 3994.0000000000005 |            3689.15 |
| Music      |   1999 |     1 |  7014.379166666668 |  3408.120000000001 |           14101.77 | 3877.5200000000004 |
| Shoes      |   1999 |     3 |  6930.184166666667 |            3333.08 |            3091.93 |  5277.389999999999 |
| Children   |   1999 |     5 |  6244.496666666667 |            2648.52 | 3340.0400000000004 | 3061.5099999999998 |
| Shoes      |   1999 |     4 |  7151.230833333334 | 3559.3299999999995 |  4220.400000000001 | 3908.6499999999996 |
| Shoes      |   1999 |     1 |  6915.220833333333 | 3327.9599999999996 | 15438.890000000001 | 3192.5099999999998 |
| Women      |   1999 |     7 |  6575.875833333334 |            2996.45 | 3738.1300000000006 |            8393.38 |
| Music      |   1999 |     6 |  7960.886666666666 |  4391.209999999999 | 4179.1900000000005 |            3989.75 |
| Men        |   1999 |     1 |          7482.2925 | 3913.1000000000004 | 17580.610000000004 |            4660.72 |
| Music      |   1999 |     3 |  7665.456666666665 |            4103.03 | 3611.3599999999997 |            4931.39 |
| Music      |   1999 |     1 |  7305.858333333334 |            3746.83 | 13654.320000000002 | 2838.8399999999997 |
| Women      |   1999 |     1 |  6473.300833333334 |            2918.56 | 12318.300000000003 |            3441.14 |
| Children   |   1999 |     3 |  6244.496666666667 | 2692.3999999999996 |            3417.66 | 3340.0400000000004 |
| Shoes      |   1999 |     5 |  6915.220833333333 | 3363.5300000000007 |             4413.6 |            4768.59 |
| Music      |   1999 |     3 |  7857.552499999999 |            4320.92 |            4822.07 |            4777.33 |
| Music      |   1999 |     4 |  7305.858333333334 | 3787.8900000000003 |            4682.73 |            4897.65 |
| Shoes      |   1999 |     2 |  6564.118333333332 |            3046.29 |            4298.34 |  4688.650000000001 |
| Shoes      |   1999 |     5 |  7090.455000000001 | 3598.3200000000006 | 3885.4199999999996 |            3233.38 |
| Women      |   1999 |     7 |           6415.255 | 2931.4000000000005 | 3859.5399999999995 |  8772.649999999998 |
| Men        |   1999 |     3 |  7469.455000000001 |            3997.96 |            4454.62 |  4856.960000000001 |
| Men        |   1999 |     4 |          7536.1675 | 4070.5300000000007 |  4616.460000000001 |  5193.620000000001 |
| Children   |   1999 |     4 |  7658.071666666667 |            4205.54 |            3665.09 | 3960.4799999999996 |
| Women      |   1999 |     6 |  6735.325000000001 |            3289.16 |            3810.92 |            3421.56 |
| Women      |   1999 |     4 |  6473.300833333334 |            3029.66 |            4066.84 |            4452.18 |
| Women      |   1999 |     4 |  6030.885833333334 |            2590.14 |  4099.639999999999 | 3348.4100000000003 |
| Children   |   1999 |     2 |  7658.071666666667 |            4222.77 |  4822.930000000001 |            3665.09 |
| Music      |   1999 |     6 |  7106.589166666668 | 3672.1499999999996 |            4348.83 |            4135.57 |
| Shoes      |   1999 |     6 |            6846.16 | 3427.2999999999993 | 4007.1100000000006 |            4791.14 |
| Shoes      |   1999 |     7 |  6930.184166666667 |            3513.24 |            4324.92 |  8995.989999999998 |
| Women      |   1999 |     5 |  6575.875833333334 |            3159.42 |            3352.17 | 3738.1300000000006 |
| Children   |   1999 |     3 |  6501.399166666666 | 3088.8199999999997 |            4844.96 |            3211.37 |
| Shoes      |   1999 |     7 |  7151.230833333334 |            3739.28 |  4889.139999999999 |           10018.74 |
| Children   |   1999 |     6 |  6834.036666666667 |            3438.01 |            3216.33 |            3933.33 |
| Men        |   1999 |     4 |          6092.5275 | 2705.4700000000003 | 3580.9300000000003 |            3946.78 |
| Men        |   1999 |     1 |  7469.455000000001 |            4084.37 | 15702.939999999997 |            4454.62 |
| Shoes      |   1999 |     5 |  6930.184166666667 |            3547.09 |  5277.389999999999 |            4324.92 |
| Music      |   1999 |     7 |  6544.270833333332 |            3169.16 | 3599.0099999999993 |  9674.190000000002 |
| Music      |   1999 |     6 |  7665.456666666665 |            4303.32 | 4646.2300000000005 |  6216.039999999999 |
| Shoes      |   1999 |     2 |  7151.230833333334 |            3794.46 |            4267.59 |  4220.400000000001 |
| Music      |   1999 |     6 |  7305.858333333334 |            3950.95 |            4897.65 |            4674.19 |
| Children   |   1999 |     1 |  6597.224999999999 | 3245.5699999999997 | 13023.890000000001 |            2872.68 |
| Music      |   1999 |     3 |  7014.379166666668 | 3662.7599999999998 | 3877.5200000000004 | 4038.4900000000002 |
| Music      |   1999 |     2 |  5840.587500000001 |            2490.51 |            3128.09 |            3883.31 |
| Men        |   1999 |     2 |          6092.5275 | 2749.4500000000003 | 3613.9999999999995 | 3580.9300000000003 |
| Shoes      |   1999 |     6 |           7379.815 | 4039.4700000000003 |            4624.27 |  4832.280000000001 |
| Children   |   1999 |     5 |  6550.796666666666 |            3218.37 | 2541.5099999999993 |            4617.26 |
+------------+--------+-------+--------------------+--------------------+--------------------+--------------------+
100 rows in set (2.31 sec)

StarRocks > with v1 as(
    ->   select i_category, i_brand, cc_name, d_year, d_moy,
    ->         sum(cs_sales_price) sum_sales,
    ->         avg(sum(cs_sales_price)) over
    ->           (partition by i_category, i_brand,
    ->                      cc_name, d_year)
    ->           avg_monthly_sales,
    ->         rank() over
    ->           (partition by i_category, i_brand,
    ->                      cc_name
    ->            order by d_year, d_moy) rn
    ->   from public_item_rt, public_catalog_sales_rt, public_date_dim_rt, public_call_center_rt
    ->   where cs_item_sk = i_item_sk and
    ->        cs_sold_date_sk = d_date_sk and
    ->        cc_call_center_sk= cs_call_center_sk and
    ->        (
    ->          d_year = 1999 or
    ->          ( d_year = 1999-1 and d_moy =12) or
    ->          ( d_year = 1999+1 and d_moy =1)
    ->        )
    ->   group by i_category, i_brand,
    ->           cc_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.cc_name = v1_lag.cc_name and
    ->        v1.cc_name = v1_lead.cc_name and
    ->        v1.rn = v1_lag.rn + 1 and
    ->        v1.rn = v1_lead.rn - 1)
    -> select  *
    -> from v2
    -> where  d_year = 1999 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;
+------------+--------+-------+-------------------+--------------------+--------------------+--------------------+
| i_category | d_year | d_moy | avg_monthly_sales | sum_sales          | psum               | nsum               |
+------------+--------+-------+-------------------+--------------------+--------------------+--------------------+
| Men        |   1999 |     1 |         7536.1675 |            2822.22 |           15366.77 |            4568.91 |
| Music      |   1999 |     2 | 7305.858333333334 |            2838.84 | 3746.8300000000004 |            4682.73 |
| Shoes      |   1999 |     7 | 6915.220833333333 |            2512.46 |  4768.589999999999 |            8884.76 |
| Men        |   1999 |     7 |         7482.2925 |             3324.6 |            3422.34 | 11231.749999999996 |
| Men        |   1999 |     7 | 6910.990833333333 | 2759.9500000000003 |             4606.6 |            8151.58 |
| Music      |   1999 |     1 | 7960.886666666665 |            3817.58 | 14931.199999999999 |  4740.459999999999 |
| Music      |   1999 |     5 | 7857.552500000001 |            3718.35 |            4777.33 |             4717.9 |
| Children   |   1999 |     2 | 7660.096666666667 | 3548.7499999999995 | 3736.2899999999995 |            5145.36 |
| Men        |   1999 |     6 |         7482.2925 |            3422.34 |            4822.97 |             3324.6 |
| Music      |   1999 |     2 | 7665.456666666665 | 3611.3600000000006 |            6319.82 |            4103.03 |
| Men        |   1999 |     2 | 7322.909166666665 |            3300.35 |            4358.89 |            4926.22 |
| Children   |   1999 |     4 | 6550.796666666666 | 2541.5099999999998 |               3559 |            3218.37 |
| Children   |   1999 |     3 | 7658.071666666667 |            3665.09 | 4222.7699999999995 |  4205.540000000001 |
| Music      |   1999 |     7 | 7960.886666666665 | 3989.7499999999995 |            4391.21 | 11045.380000000001 |
| Shoes      |   1999 |     5 | 6563.098333333332 |            2623.85 |            3831.07 |            4494.57 |
| Children   |   1999 |     4 | 6834.036666666668 |            2896.51 |  5840.070000000001 |            3216.33 |
| Children   |   1999 |     1 | 7660.096666666667 | 3736.2899999999995 | 14178.720000000001 | 3548.7499999999995 |
| Women      |   1999 |     2 | 7017.650833333333 | 3098.8799999999997 | 3740.9500000000007 |  4299.150000000001 |
| Men        |   1999 |     5 | 7493.482500000001 |            3595.76 |               4971 | 3726.7599999999998 |
| Men        |   1999 |     1 | 6797.435833333333 | 2917.3900000000003 |            12037.3 |            4138.65 |
| Shoes      |   1999 |     1 | 7379.814999999999 |            3509.89 | 14291.370000000003 |            4788.05 |
| Music      |   1999 |     4 | 7647.739999999999 | 3781.8700000000003 |  3913.830000000001 |  4904.369999999999 |
| Shoes      |   1999 |     4 | 7379.814999999999 | 3514.1400000000003 |            3657.49 |            4624.27 |
| Shoes      |   1999 |     6 | 7090.454999999999 |            3233.38 | 3598.3200000000006 |  6011.689999999999 |
| Shoes      |   1999 |     2 | 6930.184166666667 | 3091.9300000000003 |            3755.38 | 3333.0799999999995 |
| Shoes      |   1999 |     3 |           6846.16 |            3019.19 |             4003.6 |            3592.21 |
| Children   |   1999 |     3 | 7020.350000000001 |            3201.74 | 4938.1900000000005 |            3874.13 |
| Children   |   1999 |     5 | 7660.096666666667 |            3851.82 |            5433.95 |             5087.6 |
| Women      |   1999 |     5 | 7017.650833333333 | 3212.8199999999997 |  5203.349999999999 |            4474.02 |
| Music      |   1999 |     5 | 7960.886666666665 |            4179.19 |  4726.960000000001 |            4391.21 |
| Shoes      |   1999 |     2 | 6563.098333333332 |            2791.95 |            4232.09 |            3822.87 |
| Men        |   1999 |     7 |         7536.1675 |            3766.61 |            4447.43 | 10746.779999999999 |
| Men        |   1999 |     6 | 7493.482500000001 | 3726.7599999999998 |            3595.76 |            4645.91 |
| Children   |   1999 |     7 | 6501.399166666666 |            2743.73 | 3732.2300000000005 |  8546.300000000001 |
| Music      |   1999 |     3 | 7647.739999999999 |  3913.830000000001 | 5398.2300000000005 | 3781.8700000000003 |
| Children   |   1999 |     2 | 6597.224999999999 | 2872.6800000000003 | 3245.5700000000006 |            3981.84 |
| Shoes      |   1999 |     2 | 6915.220833333333 |            3192.51 | 3327.9599999999996 |             4322.2 |
| Shoes      |   1999 |     3 | 7379.814999999999 |            3657.49 |            4788.05 | 3514.1400000000003 |
| Women      |   1999 |     2 | 6575.875833333333 | 2871.6099999999997 |  4816.299999999999 | 4012.5200000000004 |
| Shoes      |   1999 |     7 | 6563.098333333332 | 2860.8799999999997 |            4494.57 |            9707.17 |
| Music      |   1999 |     2 | 6544.270833333333 | 2844.8900000000003 | 2880.0699999999997 |            4428.12 |
| Children   |   1999 |     5 | 7658.071666666667 |  3960.479999999999 |  4205.540000000001 |            5555.67 |
| Children   |   1999 |     2 | 6349.134166666666 |            2657.28 |            3424.54 |            3589.34 |
| Music      |   1999 |     1 | 7857.552500000001 |            4170.07 | 17003.920000000002 |            4822.07 |
| Children   |   1999 |     3 | 6636.591666666667 | 2954.3500000000004 | 3456.3099999999995 |            4807.28 |
| Men        |   1999 |     7 | 7469.455000000001 |            3794.75 |             4925.2 |            7872.42 |
| Music      |   1999 |     4 | 6544.270833333333 |            2872.12 |            4428.12 | 3693.2799999999993 |
| Men        |   1999 |     4 | 6910.990833333333 |            3243.06 |            4144.89 |            5533.21 |
| Music      |   1999 |     1 | 6544.270833333333 | 2880.0699999999997 | 14811.550000000001 | 2844.8900000000003 |
| Women      |   1999 |     2 | 6916.938333333333 |            3268.54 |            4739.63 | 3897.8500000000004 |
| Music      |   1999 |     3 | 7960.886666666665 |            4316.31 |  4740.459999999999 |  4726.960000000001 |
| Men        |   1999 |     1 | 6634.776666666668 |            2991.55 |           11153.02 | 3585.3199999999997 |
| Men        |   1999 |     7 | 7322.909166666665 |            3698.98 |  4327.460000000001 |  8961.509999999998 |
| Children   |   1999 |     5 | 6834.036666666668 |            3216.33 |            2896.51 |            3438.01 |
| Music      |   1999 |     4 | 6743.225000000001 | 3135.5699999999997 |               3994 |            3689.15 |
| Music      |   1999 |     1 | 7014.379166666668 |            3408.12 |           14101.77 |            3877.52 |
| Shoes      |   1999 |     3 | 6930.184166666667 | 3333.0799999999995 | 3091.9300000000003 |            5277.39 |
| Children   |   1999 |     5 | 6244.496666666667 | 2648.5199999999995 |            3340.04 | 3061.5099999999998 |
| Shoes      |   1999 |     4 | 7151.230833333334 |            3559.33 |  4220.400000000001 |  3908.649999999999 |
| Shoes      |   1999 |     1 | 6915.220833333333 | 3327.9599999999996 |           15438.89 |            3192.51 |
| Women      |   1999 |     7 | 6575.875833333333 | 2996.4499999999994 |            3738.13 |  8393.380000000001 |
| Music      |   1999 |     6 | 7960.886666666665 |            4391.21 |            4179.19 | 3989.7499999999995 |
| Men        |   1999 |     1 |         7482.2925 |             3913.1 |           17580.61 |  4660.719999999999 |
| Music      |   1999 |     3 | 7665.456666666665 |            4103.03 | 3611.3600000000006 |            4931.39 |
| Music      |   1999 |     1 | 7305.858333333334 | 3746.8300000000004 | 13654.320000000002 |            2838.84 |
| Women      |   1999 |     1 | 6473.300833333334 |            2918.56 |            12318.3 |            3441.14 |
| Children   |   1999 |     3 | 6244.496666666667 |             2692.4 | 3417.6600000000003 |            3340.04 |
| Shoes      |   1999 |     5 | 6915.220833333333 |            3363.53 |             4413.6 |  4768.589999999999 |
| Music      |   1999 |     3 | 7857.552500000001 |            4320.92 |            4822.07 |            4777.33 |
| Music      |   1999 |     4 | 7305.858333333334 | 3787.8900000000003 |            4682.73 |  4897.650000000001 |
| Shoes      |   1999 |     2 | 6564.118333333332 |            3046.29 |            4298.34 |  4688.650000000001 |
| Shoes      |   1999 |     5 | 7090.454999999999 | 3598.3200000000006 |            3885.42 |            3233.38 |
| Women      |   1999 |     7 |          6415.255 |             2931.4 |            3859.54 |            8772.65 |
| Men        |   1999 |     3 | 7469.455000000001 | 3997.9600000000005 |            4454.62 |            4856.96 |
| Men        |   1999 |     4 |         7536.1675 | 4070.5300000000007 |            4616.46 |  5193.620000000001 |
| Children   |   1999 |     4 | 7658.071666666667 |  4205.540000000001 |            3665.09 |  3960.479999999999 |
| Women      |   1999 |     6 | 6735.325000000001 |            3289.16 |  3810.920000000001 | 3421.5599999999995 |
| Women      |   1999 |     4 | 6473.300833333334 | 3029.6600000000003 |            4066.84 |            4452.18 |
| Women      |   1999 |     4 | 6030.885833333334 | 2590.1400000000003 |            4099.64 |            3348.41 |
| Children   |   1999 |     2 | 7658.071666666667 | 4222.7699999999995 |            4822.93 |            3665.09 |
| Music      |   1999 |     6 | 7106.589166666668 |            3672.15 |            4348.83 |            4135.57 |
| Shoes      |   1999 |     6 |           6846.16 |             3427.3 |            4007.11 |  4791.139999999999 |
| Shoes      |   1999 |     7 | 6930.184166666667 |            3513.24 |            4324.92 |            8995.99 |
| Women      |   1999 |     5 | 6575.875833333333 |            3159.42 |            3352.17 |            3738.13 |
| Children   |   1999 |     3 | 6501.399166666666 |            3088.82 |            4844.96 | 3211.3699999999994 |
| Shoes      |   1999 |     7 | 7151.230833333334 |            3739.28 |            4889.14 |           10018.74 |
| Children   |   1999 |     6 | 6834.036666666668 |            3438.01 |            3216.33 |            3933.33 |
| Men        |   1999 |     4 |         6092.5275 | 2705.4700000000003 | 3580.9300000000003 | 3946.7800000000007 |
| Men        |   1999 |     1 | 7469.455000000001 |            4084.37 | 15702.939999999999 |            4454.62 |
| Shoes      |   1999 |     5 | 6930.184166666667 | 3547.0899999999997 |            5277.39 |            4324.92 |
| Music      |   1999 |     7 | 6544.270833333333 | 3169.1600000000003 |            3599.01 |  9674.189999999999 |
| Music      |   1999 |     6 | 7665.456666666665 |            4303.32 |            4646.23 |  6216.039999999999 |
| Shoes      |   1999 |     2 | 7151.230833333334 |            3794.46 |  4267.589999999999 |  4220.400000000001 |
| Music      |   1999 |     6 | 7305.858333333334 | 3950.9500000000003 |  4897.650000000001 |            4674.19 |
| Children   |   1999 |     1 | 6597.224999999999 | 3245.5700000000006 | 13023.890000000001 | 2872.6800000000003 |
| Music      |   1999 |     3 | 7014.379166666668 | 3662.7599999999998 |            3877.52 |            4038.49 |
| Music      |   1999 |     2 | 5840.587499999999 | 2490.5099999999998 |            3128.09 | 3883.3099999999995 |
| Men        |   1999 |     2 |         6092.5275 | 2749.4500000000003 |               3614 | 3580.9300000000003 |
| Shoes      |   1999 |     6 | 7379.814999999999 | 4039.4699999999993 |            4624.27 |  4832.280000000001 |
| Children   |   1999 |     5 | 6550.796666666666 |            3218.37 | 2541.5099999999998 |            4617.26 |
+------------+--------+-------+-------------------+--------------------+--------------------+--------------------+
100 rows in set (3.35 sec)

[atwong]

Query profile
hudi.log

Query profile warm
hudiwarm.log