Infrequent Rider Analysis

-- SELECT

-- b."UserID",

-- COUNT(*) AS "ActivatedTicketCount"

-- FROM

-- "Behaviours" b

-- JOIN

-- "UsersFromTenants" u ON b."UserID" = u."UserID"

-- WHERE

-- b."ActivatedAtUTC" BETWEEN '2023-10-01' AND '2023-12-31'

-- AND u."ConsentedAtUTC" < '2023-10-01'

-- GROUP BY

-- b."UserID"

-- HAVING

-- COUNT(*) < 13;

SELECT

COUNT(*) AS "NumberOfUsers"

FROM (

SELECT

b."UserID"

FROM

"Behaviours" b

JOIN

"UsersFromTenants" u ON b."UserID" = u."UserID"

WHERE

b."ActivatedAtUTC" BETWEEN '2023-10-01' AND '2023-12-31'

AND u."ConsentedAtUTC" < '2023-10-01'

GROUP BY

b."UserID"

) AS SubQuery;

Count of active months per user (Q4 2023)

SELECT

b."UserID",

(CASE WHEN COUNT(CASE WHEN EXTRACT(MONTH FROM b."ActivatedAtUTC") = 10 THEN 1 END) > 0 THEN 1 ELSE 0 END

+ CASE WHEN COUNT(CASE WHEN EXTRACT(MONTH FROM b."ActivatedAtUTC") = 11 THEN 1 END) > 0 THEN 1 ELSE 0 END

+ CASE WHEN COUNT(CASE WHEN EXTRACT(MONTH FROM b."ActivatedAtUTC") = 12 THEN 1 END) > 0 THEN 1 ELSE 0 END) AS "ActiveMonthsCount"

FROM

"Behaviours" b

JOIN

"UsersFromTenants" u ON b."UserID" = u."UserID"

WHERE

b."ActivatedAtUTC" >= '2023-10-01' AND b."ActivatedAtUTC" < '2024-01-01'

AND u."ConsentedAtUTC" < '2023-10-01'

GROUP BY

b."UserID";

Want to print your doc?
This is not the way.

Try clicking the ··· in the right corner or using a keyboard shortcut (

CtrlP

) instead.