I have an app that monitor software sales. Customers can buy "full" products or get a "demo". I would like to visualise how useful the demos really are by comparing the number of purchases that were preceded by a demo to the direct purchases.
My attached table shows some example data, and also the numbers I would like to get out of this. Can you help me to figure out the best way of achieving this?
Wow, what a dirty trick (in a good way). But, it does require that the data is sorted according to date, right? This doesn't explicitly compare the dates but rather look for concatenated strings with "Demo" followed by "Full" if I understand the code correctly.
Clever! I didn't know that. And yes, it works! But, unfortunately, it is a bit too slow with millions of lines in that table. However, I think I could get what I want in the load script using GROUP BY and the same strategy, to create some sort of helper table with this information pre-calculated. I'll give it a try!