Search code examples
sqlsumcalc

How do I get SUM by date without losing a calculation that relies on summing instance

CODE:

SELECT occurred_at, 
       SUM(CASE WHEN activity_name = 'create' THEN 1 ELSE 0 END) AS has_create, 
       SUM(CASE WHEN activity_name = 'Resolve' THEN 1 ELSE 0 END) AS has_resolve
FROM  Activity_Data_Table$
WHERE (occurred_at > CURRENT_TIMESTAMP - 31)
  AND (activity_name IN ('create', 'Resolve'))
GROUP BY session_id, occurred_at
HAVING (MAX(CASE WHEN activity_name = 'create' THEN 1 ELSE 0 END) <> 0)

Trying to identify % of tickets by day, that create and resolve in same session. Tickets can't resolve before they're created. Timing doesn't matter, only if they are in same 'session'. Tickets can definitely be created in one session and resolved in another. There are other statuses, they don't matter. Don't care if something is resolved but from a different session. So the finished result would contain an additional column with % of below data with has_resolve/has_create at the daily level.

Current output from above: enter image description here

Solution

  • Something like this:

    WITH dat
AS
(
SELECT occurred_at, 
       SUM(CASE WHEN activity_name = 'create' THEN 1 ELSE 0 END) AS has_create, 
       SUM(CASE WHEN activity_name = 'Resolve' THEN 1 ELSE 0 END) AS has_resolve
FROM  Activity_Data_Table$
WHERE (occurred_at > CURRENT_TIMESTAMP - 31)
  AND (activity_name IN ('create', 'Resolve'))
GROUP BY session_id, occurred_at
HAVING (MAX(CASE WHEN activity_name = 'create' THEN 1 ELSE 0 END) <> 0)
)
SELECT occurred_at, SUM(CASE WHEN has_create + has_resolve = 2 THEN 1 ELSE 0 END) * 1.0 / COUNT(*) AS pct_resolved_in_same_session
FROM dat
GROUP BY occurred_at;