I am exploring an alternative solution to the one shown for Exercise 204.
My question is, why can we not filter out refunded values within the
WITH ab_test_categorization AS ( SELECT user_id, custom_parameters ->> 'ab_test_name' AS ab_test_name, custom_parameters ->> 'ab_test_variation' AS ab_test_variation, created_at AS categorized_at FROM mobile_analytics.events WHERE custom_parameters ->> 'ab_test_name' IS NOT NULL AND action = 'signup' ), ab_test_stats AS ( SELECT ab_test_variation AS variation, COUNT(DISTINCT(c.user_id)) / SUM(p.amount) AS arpu FROM ab_test_categorization c LEFT JOIN purchases p ON c.user_id = p.user_id WHERE ab_test_name = 'longer_onboarding_201803' AND p.refunded = FALSE GROUP BY 1 ) SELECT variation, arpu FROM ab_test_stats
Are there any performance differences between INNER JOIN and LEFT JOIN? For instance, this following subquery from lesson 207 could have been written with an INNER JOIN.
WITH user_activity AS ( SELECT u.user_id, u.created_at::date AS signup_date, e.created_at::date AS activity_date, COUNT(*) AS events_counts FROM mobile_analytics.events u LEFT JOIN mobile_analytics.events e ON e.user_id = u.user_id WHERE u.action = 'signup' GROUP BY 1, 2, 3 ORDER BY signup_date ASC, user_id ASC ) SELECT * FROM user_activity
Hi, so I learn better by knowing all the elements of the topic I’m learning, like “the big picture”, in this case I want to know the elements of a SQL query, I have the following:
I don’t know if I’m missing something, also I don’t know how the
BETWEEN - AND are catalogued
Thank you all!
I’m trying the following query:
WITH domains AS ( SELECT SPLIT_PART(email, '@', 2) AS email_domain, COUNT(*) AS recount FROM users GROUP BY email_domain ) SELECT DISTINCT(SPLIT_PART(email_domain, '.', 1)) AS maindomain, recount FROM domains GROUP BY maindomain, recount ORDER BY recount DESC
But I want to group the domain before the dot as one, for example I want all the “boogle’s” to count as one domain and sum the total of users with that domain (idependently of their country), but it keeps counting them as if they were different ids or domains.
Hi, im curious to know if this solves the same problem? The answer is correct but i want to make sure my SQL is correct as well in terms of what we’re looking at.
with joined as ( SELECT a.creative_name, a.label, a.activity_kind, a.adid, b.event_name FROM adjust.callbacks a LEFT JOIN adjust.callbacks b ON a.adid = b.adid and b.event_name = 'signup' WHERE a.tracker = 'gxel3d1' AND a.activity_kind = 'click' ), numbers as ( SELECT creative_name, count(distinct(case when event_name = 'signup' then adid end)) as signups, count(distinct(adid)) as total FROM joined GROUP BY 1 ) SELECT *, 100.0*signups/total from numbers order by 2 desc
I have a table with 3 columns: day of month, client_uk, flag_login (Y/N). My task is to group this table over by month to determine what flag_login should be for each month for every client. The client could be login a few time per month, for me, it doesn’t matter and I should determine whether the fact of client login at least 1 time per month. If it’s yes flag_login should Y, in opposite case ‘N’.
I don’t know how to solve it, because my window for window function should be client_uk and month simultaneously.
Thanks for the help in advance!