Lesson 174 - Would this work instead of the solution?

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

REPLIES

makaroni4

over 4 years Reply

Hi @jy

Let’s look at that query, looks very interesting

I see that you’re getting all clicks and signups first, then you group them by adid to count total clicks and signups. It’s pretty much the same query, but you use adid from Adjust as user identifier. Almost always it’s the case, so the approach is correct

You got lucky with adgroup_name – there’re not many of them in the dataset. Since the challenge was to look at the specific website pages we should include it to the query.

One last bit – I highly recommend using meaningful names for subqueries or alises.

Let’s look at the final query:

SELECT
    *,
    100.0 * signups / total
from numbers
order by 2 desc

I believe there’s no way someone can understand what is total and numbers . Other than that – great job, I really like how you find different ways to get these answers from data

over 4 years Reply

thanks! @makaroni4

Lesson 174 - Would this work instead of the solution?

REPLIES

WRITE A REPLY