Skip to content
Gallery
Product
Sprint Backlog
Roadmap
PRDs
Shared PRDs
Release Plans
New Markets
Campaign Playbook
Personas
Product Offerings
OKRs
Queries
Archive
Meeting Notes
Miami Kickoff
Customer Profile
Updated Onboarding Tutorial
LA Metro
Share
Explore
Queries

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 ⋯ next to your doc name or using a keyboard shortcut (
CtrlP
) instead.