Photoshop Revenue Analysis
For every customer that bought Photoshop, return a list of the customers, and the total spent on all the products except for Photoshop products.
Sort your answer by customer ids in ascending order.
Explanation: User 123 bought Photoshop, Premier Pro + After Effects, spending $150 for those products. We don't output user 234 because they didn't buy Photoshop.
This is another example of a task where it is wise to take a step back and break the question into smaller steps. Essentially, we are looking for two things:
Thus, let's start by finding all of the s that bought Photoshop.
Now, we'll be able to sum up the revenues while filtering for those customers. Keep in mind that the spend has to exclude Photoshop itself. To do so, let's insert the previous query into a clause using a subquery.
Note that we use (instead of ), as there might be several s. Then use an additional condition to exclude Photoshop with the (not equal to) operator. Finally, just sum the revenue, and group and order it by each customer as requested in the task.
This is what we should end up with:
Another way to do this would be with a , where we join the table on a filtered version of itself: