Query Review - Snowflake

Question

I have a query in snowflake that works as expected but I feel like there's gotta be a better way of doing this, so I'm checking if anyone has a better and more efficient solution to this.

I want to count how many users have SA4 and SA5. Then check if they are multi_unit or not. For the ones that are multi_unit, count how many of each of the other ST products they have.

Original Table:

AB4_ind	AB5_ind	Multi_unit	AB300_ind	AB10_ind	AB20_ind	AB30_ind
1	0	1	1	1	0	1
1	0	0	0	0	0	0
0	1	0	0	0	0	0
0	1	1	1	0	0	0
1	1	1	0	0	1	0
0	1	1	0	0	1	1

Output table wanted with query:

Product	CNT	Multi	AB300	AB10	AB20	AB30
AB4	3	1	1	1	0	1
AB5	4	3	1	0	2	1

Here's the query that works, but I feel like there's gotta be a better way of doing this. Please let me know your thoughts :) Appreciated

SELECT
'AB4' AS Product,
COUNT(*) AS CNT,
SUM(CASE WHEN MULTI_UNIT = 1 THEN 1 ELSE 0 END) AS MULTI,
SUM(AB300_IND) AS AB300,
SUM(AB10_IND) AS AB10,
SUM(AB20_IND) AS AB20,
SUM(AB30_IND) AS AB30,
FROM TABLE.VIEW.MAW
WHERE AB4_IND = 1
GROUP BY 1
UNION
SELECT
'AB5' AS Product,
COUNT(*) AS CNT,
SUM(CASE WHEN MULTI_UNIT = 1 THEN 1 ELSE 0 END) AS MULTI,
SUM(AB300_IND) AS AB300,
SUM(AB10_IND) AS AB10,
SUM(AB20_IND) AS AB20,
SUM(AB30_IND) AS AB30,
FROM TABLE.VIEW.MAW
WHERE AB5_IND = 1
GROUP BY 1

Please don't use links in your question as nobody with any security sense is going to click on a random link in a post. Please update your question with the information as editable text — NickW, Nov 26 '21 at 12:56
Hey @NickW ! Thanks for your comment. Makes a lot of sense and I have just edited it! Happy thanksgiving! — pedrofsgs, Nov 27 '21 at 13:41

leftjoin · Answer 1 · 2021-11-26T17:54:27.173

UNION is an overkill because rows are already unique after aggregation. UNION ALL will work faster, UNION does additional DISTINCT aggregation.

SELECT
'AB4' AS Product,
COUNT(*) AS CNT,
SUM(CASE WHEN MULTI_UNIT = 1 THEN 1 ELSE 0 END) AS MULTI,
SUM(AB300_IND) AS AB300,
SUM(AB10_IND) AS AB10,
SUM(AB20_IND) AS AB20,
SUM(AB30_IND) AS AB30
FROM TABLE.VIEW.MAW
WHERE AB4_IND = 1
GROUP BY 1
UNION ALL                ----use UNION ALL instead of UNION
SELECT
'AB5' AS Product,
COUNT(*) AS CNT,
SUM(CASE WHEN MULTI_UNIT = 1 THEN 1 ELSE 0 END) AS MULTI,
SUM(AB300_IND) AS AB300,
SUM(AB10_IND) AS AB10,
SUM(AB20_IND) AS AB20,
SUM(AB30_IND) AS AB30
FROM TABLE.VIEW.MAW
WHERE AB5_IND = 1
GROUP BY 1

Further optimization is to get rid of UNION ALL completely... You can not use single query without union all or joins with condition like this

CASE WHEN AB4_IND = 1 THEN 'AB4'
            WHEN AB5_IND = 1 THEN 'AB5' END AS Product

and using it in groupby because in case the same row has AB4_IND and AB5_IND both equal 1, it will count only first condition in CASE (AB4).

You can still get rid of second query if you will join with constant two rows set containing Products required ('AB4'), ('AB5'), this will look shorter and may perform better:

SELECT p.Product,
       COUNT(*) AS CNT,
       SUM(CASE WHEN m.MULTI_UNIT = 1 THEN 1 ELSE 0 END) AS MULTI,
       SUM(m.AB300_IND) AS AB300,
       SUM(m.AB10_IND) AS AB10,
       SUM(m.AB20_IND) AS AB20,
       SUM(m.AB30_IND) AS AB30
  FROM (VALUES ('AB4'), ('AB5')) AS p (Product)
       INNER JOIN TABLE.VIEW.MAW m 
        ON (p.Product='AB4' and m.AB4_IND = 1) OR (p.Product='AB5' and m.AB5_IND = 1)
  WHERE (m.AB4_IND = 1) OR (m.AB5_IND = 1)
  GROUP BY p.Product;

score 0 · Answer 2 · answered Nov 26 '21 at 23:17

You can also try the UNPIVOT version of the solution:

SELECT
    PRODUCT,
    COUNT(1) AS CNT,
    SUM(CASE WHEN MULTI_UNIT = 1 THEN 1 ELSE 0 END) AS MULTI,
    SUM(AB300_IND) AS AB300,
    SUM(AB10_IND) AS AB10,
    SUM(AB20_IND) AS AB20,
    SUM(AB30_IND) AS AB30
FROM TABLE.VIEW.MAW
    UNPIVOT(PRODUCT_SELECTED FOR PRODUCT IN (AB4_IND, AB5_IND))
WHERE PRODUCT_SELECTED = 1
GROUP BY 1
;

score 0 · Answer 3 · answered Nov 28 '21 at 01:10

union mostly performs better than the OR as optimizers usually have difficulty to get the right estimation, but one thing that can help you is to limit the number of operations to once by using subquery:

SELECT
    Product,
    COUNT(*) AS CNT,
    SUM(CASE WHEN MULTI_UNIT = 1 THEN 1 ELSE 0 END) AS MULTI,
    SUM(AB300_IND) AS AB300,
    SUM(AB10_IND) AS AB10,
    SUM(AB20_IND) AS AB20,
    SUM(AB30_IND) AS AB30
from (  select 'AB4' AS Product,MULTI_UNIT,AB300_IND,AB10_IND,AB20_IND,AB30_IND
        FROM TABLE.VIEW.MAW
        WHERE AB4_IND = 1
        UNION ALL 
        select 'AB5',MULTI_UNIT,AB300_IND,AB10_IND,AB20_IND,AB30_IND
        FROM TABLE.VIEW.MAW
        WHERE AB5_IND = 1
) t group by product

Query Review - Snowflake

3 Answers3