CODEWITHSUNDEEP
sqloracle-database
aywhatsallthis
aywhatsallthis

Reputation: 35

Calculating the count each day after a dynamic timestamp for 2 days in Oracle

Let's say I have a two tables in my Oracle database...

One table stores when a new collection is added

+---------------+----------------------+
| collection_id |      created_ts      |
+---------------+----------------------+
|           001 | 02-JAN-2019 19:40:47 |
|           002 | 04-JAN-2019 00:05:05 |
+---------------+----------------------+

The other table stores each action a user takes against a collection

+---------------+---------------------------------+
| collection_id |            action_ts            |
+---------------+---------------------------------+
|           001 | 02-JAN-19 07.41.08.869000000 PM |
|           001 | 02-JAN-19 07.41.29.440000000 PM |
|           001 | 02-JAN-19 08.34.42.097000000 PM |
|           001 | 02-JAN-19 09.27.02.433000000 PM |
|           001 | 03-JAN-19 03.54.42.098000000 PM |
|           001 | 07-JAN-19 08.04.55.075000000 PM |
|           001 | 08-JAN-19 07.29.38.727000000 PM |
+---------------+---------------------------------+

My goal is to determine a daily action count for each collection for up to 2 days after the collection is created.

The example output would look something like

+---------------+----------------+----------------------+----------------------+
| collection_id | actions_day_of | actions_1_days_after | actions_2_days_after |
+---------------+----------------+----------------------+----------------------+
|           001 |              4 |                    1 |                    0 |
+---------------+----------------+----------------------+----------------------+

Is there some combination of counts and case statements I can use to get this output, or does this have to be some union of 7 separate queries to handle all possible days of the week a collection could be created?

Upvotes: 0

Views: 98

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270883

I would just use conditional aggregation:

SELECT T1.COLLECTION_ID,
       (CASE WHEN TRUNC(T2.ACTION_TS) = TRUNC(T1.CREATED_TS) 
             THEN 1 ELSE 0
        END) as actions_day_of,
       (CASE WHEN TRUNC(T2.ACTION_TS) = TRUNC(T1.CREATED_TS) + INTERVAL '1' DAY 
             THEN 1 ELSE 0
        END) as actions_1_day_after,
       (CASE WHEN TRUNC(T2.ACTION_TS) = TRUNC(T1.CREATED_TS) + INTERVAL '2' DAY 
             THEN 1 ELSE 0
        END) as actions_2_day_after,
       (CASE WHEN TRUNC(T2.ACTION_TS) > TRUNC(T1.CREATED_TS) + INTERVAL '2' DAY 
             THEN 1 ELSE 0
        END) as actions_2plus_days_after
     TABLE2 T2
     ON T1.COLLECTION_ID = T2.COLLECTION_ID
GROUP BY T1.COLLECTION_ID;

Notice the last column. It is much simpler to have inequalities compared to PIVOT.

Upvotes: 0

Popeye
Popeye

Reputation: 35930

You can take advantage of the PIVOT for achieving the desired result.

SELECT * FROM
( SELECT T1.COLLECTION_ID,
         TRUNC(T2.ACTION_TS) - TRUNC(T1.CREATED_TS) AS DIFF
    FROM TABLE1 T1
         JOIN TABLE2 T2 ON T1.COLLECTION_ID = T2.COLLECTION_ID
) PIVOT (
    COUNT ( 1 ) 
    FOR DIFF IN ( 0 AS ACTIONS_DAY_OF, 
                  1 AS ACTIONS_1_DAYS_AFTER, 
                  2 AS ACTIONS_2_DAYS_AFTER )
)

Cheers!!

Upvotes: 2

Related Questions