TomThomson (4/18/2015)
Hugo Kornelis (4/15/2015)
But note that supplying an offset only produces the expected results if you can be sure that there is exactly one row per month in the sales table. (Or, if you use the PARTITION BY clause, exactly one row per distinct set of values of the partition columns and per month).LAG simply counts rows, it does not look at the actual values.
I think that the wording of the question makes it quite clear that one is looking at something that delivers the sales for a month - after all, it's made absolutely clear that that's what is being compared. What makes you think that the that it is not sensible to assume (given the wording of the question) that the query refers to a table rather than to some row set delivered by various joins, aggregates, and restrictions which result in monthly data?
Oh, I absolutely am with you on the assumption that the data being queries is already aggregated on a per-month basis. I now, upon rereading, realise that my comment can be interpreted ambiguously.
When writing that comment, I was only thinking of data that, for whatever reason, misses months.
(In some businesses, it is not uncommon for a salesperson to sometimes have to work multiple months on a single sale, so aggregating per salesperson and per month can easily result in getting less than 12 rows per year for a salesperson - if you need to compare to last year, you either have to use a different method, or you have to join to a calendar table to guarantee that every month is present)