[HN Gopher] Everything is a funnel, but SQL doesn't get it
       ___________________________________________________________________
        
       Everything is a funnel, but SQL doesn't get it
        
       Author : mikpanko
       Score  : 15 points
       Date   : 2022-06-23 17:55 UTC (5 hours ago)
        
 (HTM) web link (motifanalytics.medium.com)
 (TXT) w3m dump (motifanalytics.medium.com)
        
       | PaulHoule wrote:
       | See
       | 
       | https://en.wikipedia.org/wiki/Complex_event_processing
       | 
       | and
       | 
       | https://en.wikipedia.org/wiki/Datalog
        
       | mr_gibbins wrote:
       | Not to play Code Golf here but the premise that retention takes
       | 50+ lines of SQL is plain wrong.
       | 
       | 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
        
         | garciasn wrote:
         | As a DE by trade, working in marketing and customer analytics,
         | I agreed with with the premise that analyzing data as they said
         | was what happens most often; however, like you, I disagree that
         | it's as complicated as this post claims.
         | 
         | 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.
        
         | akhmatova wrote:
         | _Not to play Code Golf here but the premise that retention
         | takes 50+ lines of SQL is plain wrong._
         | 
         | 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.
        
         | saltcured wrote:
         | Yes, the title could be "author does not get SQL"...
         | 
         | 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);
        
       | ZseeBrz wrote:
       | Process Mining is a relatively new field of data analytics,
       | focusing on exactly these types of questions using workflow or
       | activity sequence data. Celonis, one of the biggest players in
       | the field have even developed a query languages called PQL for
       | querying these kind of information from "event logs"
        
       | [deleted]
        
       ___________________________________________________________________
       (page generated 2022-06-23 23:02 UTC)