CODEWITHSUNDEEP
mysqlsql
AnnaSm
AnnaSm

Reputation: 2300

SQL Query to determine % of users in a cohort sent 1 or more invitations

I have the following tables:

USERS: id, created_at, onboarding_started_at
INVITATIONS: id, user_id

I'm working to write a query that shows the % of new users who sent 1 or more invites. Here's what I have so far:

SELECT floor(datediff(u.created_at, curdate()) / 7) AS weeks_ago,
       count(DISTINCT u.id) AS "New Users in Cohort", 
       count(DISTINCT i.id) AS "Total Invites in Cohort",
       count(DISTINCT i.user_id) AS "Uniq Users who invited Cohort",
count(DISTINCT u.id) / count(DISTINCT i.user_id) AS "% who invite"
FROM users u 
LEFT JOIN invitations i
     ON u.id = i.user_id
WHERE u.onboarding_started_at IS NOT NULL
GROUP BY weeks_ago;

Currently this is outputting, "% who invite" incorrectly. Example data:

weeks_ago | New Users in Cohort | Uniq Users who Invited Cohort | % who invite
-32 | 12 |  557 |  12 1.0000

In the above example, % who invite is showing 1.0000

What should I do to fix "% who invite" ? Am I computing Uniq Users who invited Cohort incorrectly?

Thanks

Upvotes: 1

Views: 57

Answers (1)

SQL B
SQL B

Reputation: 121

Have you tried casting the values as DECIMAL?

CAST(count(DISTINCT u.id) AS DECIMAL(7,2)) / CAST(count(DISTINCT i.user_id) AS DECIMAL(7,2))

Upvotes: 1

Related Questions