Ilkka Peltola

Have you ever struggled with calculating your active users per day, or month? How about your new activations or churned users? It's a common problem, but often the first solution you find will not scale. You should avoid sub queries, and use a window function instead. But that's only a part of the solution. Assuming your active user definition is "someone who has signed in within 7 days" and you have this data in a table with columns user_id , timestamp . The first thing you want to do is calculate when a user activates for the first time, when they churn and when they resurrect. CREATE OR REPLACE VIEW "vw_login" AS SELECT * , LEAST (LEAD("timestamp") OVER w, "timestamp" + 7) AS "activeExpiry" , CASE WHEN LAG("timestamp") OVER w IS NULL THEN true ELSE false AS "activated" , CASE WHEN LEAD("timestamp") OVER w IS NULL THEN true WHEN LEAD("timestamp") OVER w - "t...

Back when we were deciding on an SQL flavor at Zervant , the fact that MySQL didn't have WINDOW functions was a deal breaker. We had plenty of MySQL experience and not that much in PostgreSQL but I had to go with PostgreSQL anyway - just because of WINDOW functions. But that's about to change, as MySQL is getting this support soon , too. Imagine you need to calculate the number of active users you have on any given day. Now imagine that the way you have defined an active user is as someone who has done something  on your service within e.g. 30 days. The something  might be e.g. uploaded something, downloaded something, made a purchase, edited their profile - doesn't matter. And imagine you want to split by dimensions. Without WINDOW functions you don't have good options. You can accomplish this by either sub queries, which gets very expensive: SELECT t.id, t.count, (SELECT SUM(t2.count) FROM t as t2 WHERE t2.id or MySQL variables, which...