I have noticed that there are a lot of places in which people have used subquery within select/from/where or some other clauses. Can you please share some scenrios & guidelines around when to use a sub-query in which clause.
Kind regards, Tushar
I don’t quite understand why this sub query (below) is turning up zeros, rather than purchase rate to question 87. I see from the answer I could have simplified this into one query, but I don’t understand why this way doesn’t work as well? Thanks for your help!
with tabletime as ( select utm_campaign, COUNT(*) AS userscount, Count(CASE WHEN status = 'customer' THEN id END) AS payingcustomers from users WHERE utm_campaign IS NOT NULL GROUP BY utm_campaign ) SELECT utm_campaign, 100 * (payingcustomers / userscount) as purchase_rate FROM tabletime
with reference to chapter “181. Counting app releases per month” & query - “How many releases Bindle had in February, 2018?” - I’m unable to understand on how’s the following solution different from the one proposed in chapter & why isn’t this correct?
SELECT COUNT( distinct app_version) FROM adjust.callbacks where to_char(created_at, 'yyyy-mm') = '2018-02'
With reference to the CTE -
total_users in following query for this chapter, my query is that why isn’t
distinct used with
COUNT(*) AS users_count
WITH spends AS ( SELECT utm_source, SUM(amount) AS total_spend FROM marketing_spends GROUP BY 1 ), total_users AS ( SELECT utm_source, COUNT(*) AS users_count FROM users WHERE utm_source IS NOT NULL GROUP BY 1 ) SELECT s.utm_source, total_spend / users_count AS CPA FROM spends s INNER JOIN total_users u ON s.utm_source = u.utm_source
I tried solving 106 problem using the following query:
SELECT utm_campaign, SUM(amount) AS total_revenue, COUNT(DISTINCT a.id) AS total_users, SUM(amount)/COUNT(DISTINCT b.id) AS ARPU FROM purchases a JOIN users b ON a.user_id = b.id AND a.refunded = FALSE -- WHERE utm_campaign IS NOT NULL GROUP BY 1 ORDER BY 4 DESC
which should be quite similar with the proposed solution, the difference is on the order of FROM clause. I used purchase table before user table and it gives a very different results.
I’d like to understand why is this the case as this is a quite surprising SQL behavior to me.
In 105. a great and important point is made in the difference of limiting a query in the join conditions vs. limiting a query in the where clause.
I understand the scenario of why limiting the query in the join condition makes sense (filtering the purchases table to only consider all purchases that were not refunded) and I see how using the where clause ultimately returns wrong output (filtering in the where clause will ignore the ‘negative space’ of all the NULLs in purchase columns for users who did not make a purchase).
I tried challenging my understanding a bit and came up with the ‘b’ CTE below (‘a’ CTE is the correct query as described in the exercise). My question is then: how am I making a logical error in trying to utilise the where clause in the ‘b’ CTE below? As far as I can see, the difference in counts between ‘a’ and ‘b’ is 104 rows (purchases that were refunded?).
WITH a AS ( SELECT SUM(amount) / COUNT(DISTINCT(u.id)) AS ARPU FROM users u LEFT JOIN purchases p ON u.id = p.user_id AND refunded = FALSE ), b AS ( SELECT SUM(amount) / COUNT(DISTINCT(u.id)) AS ARPU FROM users as u LEFT JOIN purchases AS p ON u.id = p.user_id WHERE refunded IS NULL OR refunded = FALSE ) SELECT * FROM b -- SELECT * FROM a /* The 'a' CTE above is correct. I can see that 'b' CTE is not correct, but I'm unable to wrap my mind around why, for the 'b' version, the 'amount' is greater in relation to the distinct user count. */
I’m in lesson 98 “Grouping and counting with LEFT JOIN”, but I noticed something, when doing the following query:
SELECT name, COUNT(user_id) FROM books b LEFT JOIN books_users u ON u.book_id = b.id GROUP BY 1 ORDER BY 2 ASC
You actually get some books with 0 values, because nobody has started reading them. But then for avoiding duplicated values from same users we add the
DISTINCT, however, all books now again have at least a value of 1, I’m not sure why this happens.
SELECT name, COUNT(DISTINCT(user_id)) FROM books b LEFT JOIN books_users u ON u.book_id = b.id GROUP BY 1 ORDER BY 2 DESC
Hi, I’m doing the exercise “Identifying the most popular book in the catalogue”, I actually arrived to the same answers, altough with a little different query, I want to know if my result as pure lucky or is just another way to solve it. My query was:
SELECT name, COUNT(user_id) AS number_users FROM books_users bu INNER JOIN books b ON bu.book_id = b.id GROUP BY 1 ORDER BY 2 DESC, 1 ASC
An the solution was the following:
SELECT name, COUNT(DISTINCT(bu.user_id)) FROM books_users bu INNER JOIN books b ON bu.book_id = b.id GROUP BY 1 ORDER BY 2 DESC, name ASC
Also, I want to know what’s the “general rule” to do inner joins, is there a difference which table I use first and which I join?
I’m doing the “Biggest age group in USA” exercise, however, I was trying to experiment and to a little change in the analysis by trying to visualize the groups by country, but I keep getting an error with the following query:
SELECT CASE WHEN age < 13 THEN 'kid' WHEN age < 18 THEN 'teenager' WHEN age < 25 THEN 'college' WHEN age < 35 THEN 'young adult' WHEN age < 56 THEN 'middle age' ELSE 'older adult' END AS age_group, country, COUNT(*) FROM users GROUP BY 1 GROUP BY country
Thank you all!!
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!