7 votes

Modern SQL Window Function Questions

5 comments

  1. ainar-g Link
    An interesting SQL window functions quiz I have found recently.

    An interesting SQL window functions quiz I have found recently.

    2 votes
  2. nic Link
    running averages are very interesting, thanks.

    running averages are very interesting, thanks.

    2 votes
  3. [3]
    cwagner Link
    Interesting, never heard of preceding and following. Thought it makes me wonder: What's a use case? I've mostly found examples, I guess the best thing was sales stats for the current and last...

    Interesting, never heard of preceding and following. Thought it makes me wonder: What's a use case?
    I've mostly found examples, I guess the best thing was sales stats for the current and last year, but that still seems somewhat forced.

    1 vote
    1. [2]
      Emerald_Knight Link Parent
      I'm not as familiar with SQL aggregation, but if it supports multiple stages of operations then I could see there being plenty of practical applications when applied to groups. An example would be...

      I'm not as familiar with SQL aggregation, but if it supports multiple stages of operations then I could see there being plenty of practical applications when applied to groups.

      An example would be first grouping rows by month, then subtracting the total of the previous month from the total of the current month in order to obtain a series of deltas. This data could be useful for telling you how much of some metric you've lost or gained over each month. A subsequent stage could then filter by negative deltas to show only losses. A practical concrete example might be telling you which quarters your sales dipped in, so you can examine the causes of profit loss.

      Use cases can be difficult to come up with, but generally if you need to compare X number of results relative to the current one, then preceding and following are likely the tools you'll want to use.

      1 vote
      1. cwagner Link Parent
        Oh, that's a wonderful example! Thank you :)

        Oh, that's a wonderful example! Thank you :)

        1 vote