Solution: Compare order totals over time - SQL教程

- [Instructor] In this solution, I'm using CTE or common table expression notation to save some typing, because this challenge requires joining across three tables. You don't have to, though. I just think it makes things easier to type and to read. Let's start in the middle of the query and work toward the end. I start out here by selecting from the orders table, which I've named O, and then I joined the orders dishes table, which I'm calling OD to it, so that the order ID on the orders table matches up with the order ID on the orders dishes table. That lets us associate the items in an order with the order they belong to. Then I join the dishes table, which I'm calling D, so that the dish ID field on the dishes table matches up with a dish ID field on the orders dishes table. This way, I associate the dishes contained in each order, and most importantly for our challenge, I can get the price of each dish in each order. Up here at the top of the query, I'm returning order ID from the orders table, and at the end, I'm grouping my results by that same value. This allows me to have one row per order, and then I can rely on aggregation functions to operate on each order in turn. The second item I return is the sum of the prices from the dishes table for each order, and I'm calling that this order price, as the challenge required. And the final item that I return is the difference between the price of the current order and the previous one. Sum D price is the same value we return up here, the total of the current order. I subtract from that this value here. This represents a lag of one row within a window ordered by order ID from the orders table. So this sum of prices will be the order total of the row right above whatever the current row is. For the first row, this value will be null, because there isn't a row above the first row. When we get to the second row, our difference becomes more apparent. The difference will be this row's total minus the total from the first row. And for the third row, we'll subtract the total from the second row, and so on. This is how we calculate the difference between two adjacent rows, or in our database here, the difference in total between one order and the next. I've named this value as the challenge requested, and as the challenge requested, we're only looking at orders with dates from the beginning of 2022 and later. All right, there's kind of a lot going on here, so let's run this and see if my solution solves the challenge. It looks like it does, and looking at the results here, I can see this offset of one and the difference between each pair of rows as we go down the table.