Everything is a funnel, but SQL doesn't get it
motifanalytics.medium.com
motifanalytics.medium.com
Here's an implementation (T-SQL) in less than 10 lines. Table setup script is here: https://pastebin.com/9DajyzLp . There are probably even shorter ways of doing it. Anyone who objects to hardcoded dates, just swap out with a relative DATEADD.
;WITH lastweek AS ( SELECT USERID, TS, [ACTION] FROM Users WHERE TS BETWEEN '2022-06-13' AND '2022-06-19' )
SELECT ROUND(CAST(f1.[c] AS FLOAT) / CAST(f2.[c] AS FLOAT),2) FROM
( SELECT COUNT(*) [c] FROM lastweek w INNER JOIN Users u ON w.USERID = u.USERID AND w.[ACTION] = 'A' AND u.TS BETWEEN '2022-06-20' AND '2022-06-26' ) f1,
( SELECT COUNT(*) [c] FROM Users u WHERE u.[ACTION] = 'A' ) f2
The cumbersome SQL example also produces a much different report format than the simple ratio of the second query sketch. If we are charitable and assume a DBA could define what a "login" is just like the second query form assumes, we might have a view already and not need a clunky CTE to confuse things.
SELECT
(SELECT COUNT(DISTINCT user_id) FROM logins w1 JOIN logins w2 ON (w1.user_id = w2.user_id AND (EXTRACT(WEEK, w2.timestamp) - EXTRACT(WEEK, w1.timestamp))::int = 1)
/
(SELECT COUNT(DISTINCT user_id) FROM logins);
In the relational example they give[1], that is how you'd capture "have they done X, Y, and Z" whereas the sequence is explicitly capturing the order those things were done in. If you care about that information, you would design your system for this.
The point could have been that this is actually simpler with sequences, not that it's impossible in a relational db. We already have loads of examples of how to capture this data and put it in order. But I suppose a table with a User, Action, and Timestamp column would really invalidate the point the author was trying to make. Then it's just a select and order by to get the order things happened in.
Yeah, where I stopped reading when when you drill down into that 50-line SQL query and find all these lines about unpacking JSON structs and so forth. Obviously a gratuitously bloated "see-this-is-the-alternative" example so author can pad whatever argument they were trying to make.
But even sillier is the article's premise:
Everything Is a Funnel,
No -- "Some things are funnels, more often then you might think." But that's not clickbait-y enough ("conversion-friendly" as they say in the industry), the author must have thought. And so they went with the current title that brought us all here.
Don't get me wrong -- I really like finding out about use cases where SQL doesn't do so well -- but the intellectual sloppiness (and sheer guile) of this piece (obviously an ad) is a major turn-off.
As a selling tactic, I think they’re trying to market to those who think the data engineering side of things is a big drain on time and resources and another expensive tool will help.
This is the same snake oil that’s been delivered in many different ways over the entirely of my career.
The idea of a DSL to analyse sequences of events is interesting, but I'm not convinced by their examples (I mean if you want the order of events, why store them as columns of booleans saying X happened?). They also claim without proof that tables of events are somehow 'arcane' and hard to deal with, but the examples they give aren't exactly complicated even using just SQL (the best I can give them is that they may not be solvable without writing a query, but a new query language doesn't solve that problem).