Post AWUhKZmMBd5Rt2eqf2 by SamJSharpe@mastodon.me.uk
 (DIR) More posts by SamJSharpe@mastodon.me.uk
 (DIR) Post #AWUTphTCF80VMIduDI by simon@fedi.simonwillison.net
       2023-06-08T19:03:38Z
       
       0 likes, 0 repeats
       
       Some notes on working with consecutive groups in SQL using window functions, a pattern I figured out today while playing with my Swarm checkin data https://til.simonwillison.net/sql/consecutive-groups
       
 (DIR) Post #AWUbTyuXW4ociIaRlo by matthewbadger@fosstodon.org
       2023-06-08T20:29:24Z
       
       0 likes, 0 repeats
       
       @simon most dialects don’t support filter (only Postgres and SQLite, as far as I’m aware). You can achieve the same with instead of count + filter, you add an ‘is new group’ column which casts the filter condition to an int. Then add another CTE with sum(is new group) over (blah).
       
 (DIR) Post #AWUhKZmMBd5Rt2eqf2 by SamJSharpe@mastodon.me.uk
       2023-06-08T21:34:58Z
       
       0 likes, 0 repeats
       
       @simon I discovered the LAG() and LEAD() window functions recently too and it definitely neatened up some transforms I was doing.My previous solution was to use ROW_NUMBER() and then select out the current and previous value with row_num = 1, row_num = 2, but the LAG() pattern definitely cut out a few levels of nesting and made things a lot easier.
       
 (DIR) Post #AWVaNDTf3Rdt4FBWk4 by oscherler@tooting.ch
       2023-06-09T07:51:47Z
       
       0 likes, 0 repeats
       
       @simon So the second filter counts how many times the country changed, and that’s your group ID? Smart. Was it a straightforward process to get to this?
       
 (DIR) Post #AWVb6gVZxtB28WlMbg by simon@fedi.simonwillison.net
       2023-06-09T08:00:05Z
       
       0 likes, 0 repeats
       
       @oscherler GPT-4 gave me the right solution first time, but I then spent quite a while reverse engineering it and making sure I understood how it worked myself!