I am struggling to come up with a solution to trying to convert the type column below, into 3 seperate columns which would count those event types for me. Working in Snowflake on this when creating a view.
I have this sample table which is brought up using this code to see all events that happened for 1 customer.
select id, what_id, who_id, event_date, type
from table1
where who_id like 'id_1'
| id | what_id | who_id | event_date | type |
|----|---------| -------|------------|------|
|1 |null |id_1 |2023-02-24 |CALL |
|2 |null |id_1 |2023-02-27 |EMAIL |
|3 |null |id_1 |2023-02-22 |EMAIL |
|4 |null |id_1 |2023-02-25 |OTHER |
The desired output would be unpivoting the type column in exchange for 4 new columns as below:
| id | what_id | who_id | event_date | total_calls | total_emails | total_other | total_all |
|----|---------| -------|------------|-------------|--------------|-------------|-----------|
|1 |null |id_1 |2023-02-24 |1 |0 |0 |1 |
|2 |null |id_1 |2023-02-27 |0 |1 |0 |1 |
|3 |null |id_1 |2023-02-22 |0 |1 |0 |1 |
|4 |null |id_1 |2023-02-25 |0 |0 |1 |1 |
Any help would be greatly appreciated. Thanks in advance.
If you have only three types you can do.
The columns are to wake as to give a perfect answer, so i don't know if user can have multiple what_id per day, but tis will get you on the right rrack
SELECT
MIN(what_id) what_id , who_id , event_date,
SUM(CASE WHEN type = 'CALL' THEN 1 END) total_calls.
SUM(CASE WHEN type = 'EMAIL' THEN 1 END) total_emails,
SUM(CASE WHEN type = 'OTHER' THEN 1 END) total_OTHER s ,
COUNT(*) total_all
FRM mytable
GROUP BY who_id , event_date