SQLize Online / PHPize Online  /  SQLtest Online

-- Hint: use Ctrl+Enter for SQL autocomplete CREATE TABLE feed_events ( uuid varchar(30), timestamp INT, event varchar(30) ); INSERT INTO feed_events VALUES ("9729e",1639667,"show"), ("9729e",1639716,"open"), ("9729e",1639802,"show"),("9729e",1639829,"click"), ("9729e",1639812,"show"), ("2ef",1639849,"open"),("2ef",1639929,"show"), ("2ef",1639979,"click"); select uuid, cast(count(CASE WHEN event="click" THEN 1 ELSE NULL END) as real)/count(CASE WHEN event="show" THEN 1 ELSE NULL END) as ctr from feed_events where timestamp > (select min(time) from (select min(timestamp) time from feed_events where event="open" group by uuid) t1) group by uuid order by ctr desc; with s as(select uuid, min(timestamp) time from feed_events where event="open" group by uuid) select f.uuid, f.timestamp, event, s.time from feed_events f left join s on f.uuid=s.uuid; with se as (with s as(select uuid, min(timestamp) time from feed_events where event="open" group by uuid) select f.uuid, f.timestamp, event, s.time from feed_events f left join s on f.uuid=s.uuid) delete feed_events from feed_events f left join se on f.uuid=se.uuid where timestamp-time<=0; select uuid, cast(count(CASE WHEN event="click" THEN 1 ELSE NULL END) as real)/count(CASE WHEN event="show" THEN 1 ELSE NULL END) as ctr from feed_events where timestamp > (select min(time) from (select min(timestamp) time from feed_events where event="open" group by uuid) t1) group by uuid order by ctr desc; CREATE TEMP TABLE te as select uuid, min(timestamp) time from feed_events where event="open" group by uuid; CREATE TEMP TABLE te1 as select f.uuid, f.timestamp, event, te.time from feed_events f left join te on f.uuid=te.uuid; delete from te1 where timestamp-time<=0; select uuid, count(CASE WHEN event="click" THEN 1 ELSE NULL END) as cl, count(CASE WHEN event="show" THEN 1 ELSE NULL END) as sh, cast(count(CASE WHEN event="click" THEN 1 ELSE NULL END) as real)/count(CASE WHEN event="show" THEN 1 ELSE NULL END) as ctr from te1 group by uuid; select uuid, cast(count(CASE WHEN event="click" THEN 1 ELSE NULL END) as real)/count(CASE WHEN event="show" THEN 1 ELSE NULL END) as ctr from feed_events where timestamp > (select min(time) from (select min(timestamp) time from feed_events where event="open" group by uuid) t1) group by uuid order by ctr desc;