Need guidance in forming a query in snowflake

Question

SELECT id,
    login_id,
    count,
    case when count = 0 then 'Cat_A'
       WHEN  count between 1 and 10 then 'Cat_B'
       WHEN  count >  10 then 'Cat_C' 
       WHEN count IS NULL THEN 'Cat D'
       END as Category
FROM 
    (
        select id,login_id,min(ord_count) AS count
        FROM table_1 X
            JOIN table_2 Y
                ON X.id_col = Y.id_col
        WHERE date = '2022-02-02'
            AND login_id = 'True'
        group by id,login_id
    )A
    LEFT JOIN
        (
            SELECT id,COUNT(X.ord_no) AS count_of_orders
            FROM table_1 X
            WHERE X.date = '2022-02-02'
            group by id
        )B
        ON A.id=B.id

When I join these two tables, I'm getting NULL values for the unmatched records. I need to replace those NULL records to some hardcoded value say 'XYZ'.

Any guidance on how to achieve this please?

user_43992 · Answer

So the top level select needs to name which ID it is using (other DB's don't require this snowflake does), given you are selecting from A and b.id might be missing, it should be a.id
count_of_orders is not used, so currently the LEFT JOIN to B is pointless, given your question is about LEFT JOIN this must be the column you a referring to??
The replace NULL values can be done via COALESCE or NVL or ZEROIFNULL, given the only null thing is a count, zeroifnull seems to make sense here.

which all make me think your SQL needs to look like:

SELECT 
    a.id,
    a.login_id,
    a.count,
    case 
        WHEN a.count = 0 then 'Cat_A'
        WHEN a.count between 1 and 10 then 'Cat_B'
        WHEN a.count >  10 then 'Cat_C' 
        WHEN a.count IS NULL THEN 'Cat D'
    END as Category,
    ZEROIFNULL(b.count_of_orders) as count_of_orders
FROM (
    SELECT 
        id,
        login_id,
        min(ord_count) AS count
    FROM table_1 AS X
    JOIN table_2 AS Y
        ON X.id_col = Y.id_col
    WHERE date = '2022-02-02'
        AND login_id = 'True'
    group by id,login_id
) as A
LEFT JOIN (
    SELECT 
        x.id,
        COUNT(X.ord_no) AS count_of_orders
    FROM table_1 as X
    WHERE X.date = '2022-02-02'
    group by x.id
)as B
    ON A.id=B.id

The A sub-select really should use the aliases you named X, Y so we know which tables id, login_id, ord_count, & date all come from.

Need guidance in forming a query in snowflake

1 Answers