SQLize Online / PHPize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
-- 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 * from feed_events; 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, count(CASE WHEN event="click" THEN 1 ELSE NULL END)/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; select uuid, min(timestamp) from feed_events where event="open" group by uuid; select * from feed_events where (uuid,timestamp)>(select uuid, min(timestamp) from feed_events where event="open" group by uuid); select uuid, timestamp,event, lead(event) over(partition by uuid order by timestamp) from feed_events
Stuck with a problem? Got Error? Ask ChatGPT!
Copy Clear