Reputation: 1126
CREATE TABLE dialoglog (
userid int,
appid int,
type varchar(100) ,
ds timestamp
);
userid appid type ds
1 1 imp January, 19 2016 03:14:07
1 1 click January, 20 2016 03:14:07
2 1 imp January, 19 2016 03:14:07
2 1 click January, 18 2016 03:14:07
3 6 imp January, 19 2016 03:14:07
10 1 click January, 19 2016 03:14:07
1 1 imp January, 20 2016 03:12:07
What is the CTR (click/imp) per appid ?
We want to print the CTR per group
Problem is that we have "bad" data:
Therefore, a query like this:
SELECT SUM(CASE WHEN type='click' THEN 1 else 0 END)*1.0/SUM(CASE WHEN dialoglog.type='imp' THEN 1 else 0 END) as ctr,appid
FROM dialoglog GROUP BY appid;
Would not work because of the constrain I just said. Is it possible to code all these constrain in a single mysql query?
Here is sqlfiddle to play with
For appid 1 we have 3 impression and 1 click (that is valid since for user 2 timestamp clicked < timestamp impression and for user 10 there was no impression) therefore we should have a CTR of 1/3.
For appid 6, we only have one impression => CTR = 0
Therefore I am expecting this result:
appid CTR
1 0.333
6 0
I thought about doing left join but the problem is that it will duplicate the right table:
SELECT * FROM dialoglog t1 LEFT JOIN dialoglog t2 on t1.userid=t2.userid and t1.appid=t2.appid and t1.type<>t2.type WHERE t1.type="imp" and t1.ds<t2.ds;
userid appid type ds userid appid type ds
1 1 imp January, 19 2016 03:14:07 1 1 click January, 20 2016 03:14:07
1 1 imp January, 19 2016 03:12:07 1 1 click January, 20 2016 03:14:07
Upvotes: 2
Views: 221
Reputation: 1271231
You seem to need the earliest "imp" for each user. Then you can aggregate by the transactions after that point in time:
SELECT dl.appid, SUM(dl.type = 'click') / SUM(dl.type = 'imp')
FROM dialoglog dl LEFT JOIN
(SELECT dl2.userId, MIN(ds) as imp_ds
FROM dialoglog dl2
WHERE type = 'imp'
GROUP BY dl2.userid
) dlimp
ON dl.userid = dlimp.userid
WHERE dl.ds >= dlimp.imp_ds
GROUP BY dl.appid;
Upvotes: 1