Feeling confident with Basic SQL, I wanted to upskill and learn window functions. I completed this case study using a project created by Data Analyst, Danny Ma.
To Data with Danny Case StudyDanny wants to use the data to answer a few simple questions about his customers, especially about their visiting patterns, how much money they’ve spent and also which menu items are their favourite. Having this deeper connection with his customers will help him deliver a better and more personalised experience for his loyal customers.
He plans on using these insights to help him decide whether he should expand the existing customer loyalty program - additionally he needs help to generate some basic datasets so his team can easily inspect the data without needing to use SQL.
Danny has shared with you 3 key datasets for this case study:
The following questions were proposed for data exploration:
From time to time, I would get stuck and reference YouTube video solutions. With question 10, I wasn't happy with the suggested solutions I was finding.
Since the data set was small, I got out a pen and paper to figure out the totals I should be getting. The proposed solution was off.
After some experimenting, I found that the Interval function was not operating correctly in a RANK() function, so the proper dates weren't being filtered.
I found the solution to create a date window for the filtering and was finally able to match my pen and paper solution.
I learned that it's key to trust your instincts. And it doesn't hurt to double check your work manually, if you can.
The following questions are related creating basic data tables that Danny and his team can use to quickly derive insights without needing to join the underlying tables using SQL.
Recreate the following tables output using the available data: