data question:
dialoglog
(userid int
appid int
type char , a flag either "imp" or "click"
ds timestamp
)

How would you access the quality of app?
How to compute click-through rate (in mySQL)?

Question

data question:
dialoglog
(userid int
appid int
type char , a flag either "imp" or "click"
ds timestamp
)

How would you access the quality of app?
How to compute click-through rate (in mySQL)?

Sigiloso · Accepted Answer

Assuming ctr is defined as total number clicks / total number of impressions (not counting each unique user's action)

select
	appid,
	total_click_ct / total_imp_ct as ctr
from (
	select
		appid,
		count(distinct case when flag = 'imp' then 1 else 0 end) as total_imp_ct,
		count(distinct case when flag = 'click' then 1 else 0 end) as total_click_ct,
	from table
	where ts > x and ts < y
	group by appid) table2
order by ctr desc;

Sigiloso · Answer

SELECT appid, sum(click) / count(*) AS crt FROM dialoglog GROUP BY appid;

Sigiloso · Answer

select D.appid, (select count(*) from dialoglog where appid = D.appid and flag = 'click') / count(*) as CTR
from dialoglog D
group by D.appid

SAR662 · Answer

Some pseudo SQL for our table:
create table dialoglog
(user_id int,
app_id int,
type char,
flag either “ignore" or "click"
ds timestamp
)

Some sample dialoglog values [(1,50,p,”imp”,2016-01-01),(2,50,p,”click”,2016-01-01),(3,20,p,”click”,2016-01-01),(4,50,p,”imp”,2016-01-01),(4,20,p,”imp”,2016-01-01)]


1) How would you assess the quality of app?
Since there is an app_id, I assume we are tracking multiple apps in this table. For each app, count of clicks/count of popups displayed.
if lots of data, could do this on a weekly basis using the timestamp.

How to compute click-through rate (PostgreSQL)?  
SELECT app_id,
       sum(CASE
           WHERE flag = click THEN 1 ELSE 0 END)/count(*)::float4 AS click_rate
FROM dialoglog
GROUP BY 1

Anon · Answer

Select count (case when c.user_id is not null then 1 else 0 end ) /count( case when i.user_id is not null then 1 else 0 end ) as ctr

From 
(
select  imp_user_id, imp_app_id,  imp_time, count, lead_timestamp,  c_user_id, c_app_id, max(c_time) as last_click
--- taking only last click if there are multiple clicks after single impressions 
from 
(-- impression table with impression lead 
select i.user_id as imp_user_id, i.app_id as imp_app_id, i.timestamp as imp_time, lead(i.timestamp, 1) over ( partition by i.user_id,i.app_id order by timestamp) as lead_timestamp
from table i
where i.flag='imp'
) i
left join 
(-- click table 
select c.user_id as c_user_id, c.app_id as c_app_id, c.timestamp as c_time
from table c
where c.flag ='click'
) c
on i.user_id=c.user_id
and i.app_id=c.app_id
and c_time > imp_time and c_time

Sigiloso · Answer

select  ((count(cl) :: FLOAT )/ count(imp))*100
from(
select case when type = 'imp' then userid else NULL end as imp, case when type = 'cl' then userid else NULL end as cl
from step1)

Sigiloso · Answer

How to compute click-through rate (in mySQL)? 

I assum the table name is dialoglog and flag is the name of the field for 
select flag, count(flag)
from dialoglog
group by flag

This will bring you the following results
imrpessted X 
clicked Y
The CTR will be: Y/ X+Y

Meta

Pergunta de entrevista da empresa Meta

Respostas da entrevista

Empresas seguidas

Buscas de vagas