Everything about SQL

On exercise number 40….. newbie question…… the solution used COUNT() , but I use COUNT(book_id) (because i think the question is about the book lol)… is there a rule when to use COUNT() or when to use COUNT(specific column) or they will be always the same?

And what if there is a NULL value in that specific column, so COUNT(*) will always be the better choice?

I created the following query for the exercise on lesson 114. Detecting campaign with the highest CPC:

SELECT
  utm_campaign,
  amount / clicks AS CPC
FROM marketing_spends
GROUP BY 1
ORDER BY 2 DESC

However this returns an error: column marketing_spends.amount must appear in the GROUP BY.

Why is it required to include it on the group by clause? Seems that the following query runs without errors:

SELECT
  utm_campaign,
  amount / clicks AS CPC
FROM marketing_spends
GROUP BY 1, 2
ORDER BY 2 DESC

Thank you!

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 WHERE clause?

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:

• Statements: select, from, order by
• Operators: as, or, in
• functions (here we have scalar and aggregate): count, sum, min, cast

I don’t know if I’m missing something, also I don’t know how the AND and BETWEEN - AND are catalogued

Thank you all!

Hi, can you show an example of how to calculate Retention (1,7,30 days) by monthly cohorts? I have been trying to do it for 3 days on different datasets, the result is not working. thanks

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!