3 ms·

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 secon

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);

BeefWellington 4y ago
Moreover, their sequence example for the relational database is just plain the wrong way you would go about that. 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. [1]: https://miro.medium.com/max/1026/0*NbWYw7bbEflgG-5m https://miro.medium.com/max/1026/0*NbWYw7bbEflgG-5m