3 ms·

I use between rows for rolling averages all the time. SUM() OVER (PARTITION BY columns ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) gives you a trail

I use between rows for rolling averages all the time. SUM() OVER (PARTITION BY columns ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) gives you a trailing week total.

minimaxir 6y ago
That was incidentally the exact question I was asked. (When I asked during the interview "I know you need to use a SUM window function but I'm not sure how you constrain it to the past 7 days", the interviewer replied "you don't know the BETWEEN syntax? Everyone knows that." And then the interview ended.)
- lonelappde 6y ago
  You dodged a bullet with that one.
- WoahNoun 6y ago
  Definitely not cool to use syntax as an interview question, but it is a very powerful tool so I wouldn't write it off because of one interview.
- shostack 6y ago
  "In a real world situation I'd have googled it and jogged my memory in less than a minute. Since I suspect you were more interested in how I would approach solving X, can you help refresh my memory on the syntax and we can pretend I looked it up to get back to what's important here?"
- malinens 6y ago
  sounds like he is some old guy who is afraid to loose his position and is gatekeeping so he can keep his current job...
- truculent 6y ago
  What a jerk. Sorry that happened to you.
PeterisP 6y ago
This seems fishy - if your data has a whole day gap somewhere for whatever reason (e.g. I'm thinking sales data where certain emergencies might result in an unusual, unplanned 'zero day') then that query silently gives a wrong result, IMHO a trailing week total should always include a specific date boundary calculation to ensure that it's really a week.
- WoahNoun 6y ago
  For the transactional DB sure, an analytic DB / data warehouse / or even just table would have a 0 record for the no sales day. If there are gaps in daily data, at some point they need to be filled in with 0s. You can either do that at report building or query time using a cartesian join or you can fill the nulls with 0s during the data engineering pipeline. A robust BI process should do the later. For example, if I want to plot this trailing calculation and it has gaps in the data, then the plot will be very wrong. The DE pipeline should fill in these gaps.
- artwr 6y ago
  Two ways to address this: - Join to a date dimension and coalesce nulls to 0 - use RANGE BETWEEN (only available in some DBs) on a date_difference column

I use between rows for rolling averages all the time. SUM() OVER (PARTITION BY columns ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) gives you a trailing week total.

minimaxir 6y ago

That was incidentally the exact question I was asked. (When I asked during the interview "I know you need to use a SUM window function but I'm not sure how you constrain it to the past 7 days", the interviewer replied "you don't know the BETWEEN syntax? Everyone knows that." And then the interview ended.)

lonelappde 6y ago
You dodged a bullet with that one.
WoahNoun 6y ago
Definitely not cool to use syntax as an interview question, but it is a very powerful tool so I wouldn't write it off because of one interview.
shostack 6y ago
"In a real world situation I'd have googled it and jogged my memory in less than a minute. Since I suspect you were more interested in how I would approach solving X, can you help refresh my memory on the syntax and we can pretend I looked it up to get back to what's important here?"
malinens 6y ago
sounds like he is some old guy who is afraid to loose his position and is gatekeeping so he can keep his current job...
truculent 6y ago
What a jerk. Sorry that happened to you.

PeterisP 6y ago

This seems fishy - if your data has a whole day gap somewhere for whatever reason (e.g. I'm thinking sales data where certain emergencies might result in an unusual, unplanned 'zero day') then that query silently gives a wrong result, IMHO a trailing week total should always include a specific date boundary calculation to ensure that it's really a week.

WoahNoun 6y ago
For the transactional DB sure, an analytic DB / data warehouse / or even just table would have a 0 record for the no sales day. If there are gaps in daily data, at some point they need to be filled in with 0s. You can either do that at report building or query time using a cartesian join or you can fill the nulls with 0s during the data engineering pipeline. A robust BI process should do the later. For example, if I want to plot this trailing calculation and it has gaps in the data, then the plot will be very wrong. The DE pipeline should fill in these gaps.
artwr 6y ago
Two ways to address this: - Join to a date dimension and coalesce nulls to 0 - use RANGE BETWEEN (only available in some DBs) on a date_difference column