<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Grace Paul on Medium]]></title>
        <description><![CDATA[Stories by Grace Paul on Medium]]></description>
        <link>https://medium.com/@gracechisom01?source=rss-4a9fe2c68927------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*Gbn3f59q2raVWrKjhnqgrg.jpeg</url>
            <title>Stories by Grace Paul on Medium</title>
            <link>https://medium.com/@gracechisom01?source=rss-4a9fe2c68927------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Fri, 20 Nov 2026 18:19:55 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@gracechisom01/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[8-Week SQL Challenge: Case study #1: Danny’s Diner.]]></title>
            <link>https://medium.com/@gracechisom01/8-week-sql-challenge-case-study-1-dannys-diner-cd935d2e4d05?source=rss-4a9fe2c68927------2</link>
            <guid isPermaLink="false">https://medium.com/p/cd935d2e4d05</guid>
            <dc:creator><![CDATA[Grace Paul]]></dc:creator>
            <pubDate>Thu, 11 Jan 2024 12:34:55 GMT</pubDate>
            <atom:updated>2024-01-11T12:34:55.619Z</atom:updated>
            <content:encoded><![CDATA[<p>After completing the Datacamp SQL course, I have decided to try my hands on Danny Ma’s “8-week SQL challenge” case studies. I will be working as a data analyst to help Danny Ma answer his business questions.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*8hkd46D-dAaRj1xo.png" /></figure><h3>Introduction</h3><p>Danny seriously loves Japanese food so in the beginning of 2021, he decides to embark upon a risky venture and opens up a cute little restaurant that sells his 3 favourite foods: sushi, curry, and ramen.</p><p>Danny’s Diner needs my assistance to help the restaurant stay afloat — the restaurant has captured some very basic data from their few months of operation but have no idea how to use their data to help them run the business.</p><h3>Problem Statement</h3><p>Danny 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 personalized experience for his loyal customers.</p><p>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.</p><p>Danny has provided me with a sample of his overall customer data due to privacy issues — but he hopes that these examples are enough for me to write fully functioning SQL queries to help him answer his questions!</p><p>The 3 key datasets Danny shared with me for this case study are:</p><ul><li>sales: The sales table captures all customer_id level purchases with a corresponding order_date and product_id information for when and what menu items were ordered.</li><li>menu: The menu table maps the product_id to the actual product_name and price of each menu item.</li><li>members: The final members table captures the join_date when a customer_id joined the beta version of the Danny’s Diner loyalty program.</li></ul><p>You can inspect the entity relationship diagram and example data below.</p><h3>Entity Relationship Diagram</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/720/1*COkSMH1m2-kAMEunAN2BFQ.png" /></figure><p>The SQL codes were written in the Microsoft SQL Server relational database.</p><h3>Case Study Questions</h3><ol><li>What is the total amount each customer spent at the restaurant?</li><li>How many days has each customer visited the restaurant?</li><li>What was the first item from the menu purchased by each customer?</li><li>What is the most purchased item on the menu and how many times was it purchased by all customers?</li><li>Which item was the most popular for each customer?</li><li>Which item was purchased first by the customer after they became a member?</li><li>Which item was purchased just before the customer became a member?</li><li>What is the total items and amount spent for each member before they became a member?</li><li>If each $1 spent equates to 10 points and sushi has a 2x points multiplier — how many points would each customer have?</li><li>In the first week after a customer joins the program (including their join date) they earn 2x points on all items, not just sushi — how many points do customers A and B have at the end of January?</li></ol><h3>Bonus Questions</h3><h3>Join All The Things</h3><p>The following questions are related to creating basic data tables that Danny and his team can use to quickly derive insights without needing to join the underlying tables using SQL.</p><h3>Rank All The Things</h3><p>Danny also requires further information about the ranking of customer products, but he purposely does not need the ranking for non-member purchases so he expects null ranking values for the records when customers are not yet part of the loyalty program.</p><h3>Solution</h3><ol><li>What is the total amount each customer spent at the restaurant?</li></ol><p>I used the SUM aggregate function to get the total amount spent. The GROUP BY clause was used to get the total amount for <strong>each</strong> customer. Both tables were joined on product_id.</p><pre> SELECT s.customer_id, CONCAT(&#39;$ &#39;, sum(price)) AS total_amt_spent<br> FROM sales AS s<br> INNER JOIN menu AS m<br> ON s.product_id = m.product_id<br> GROUP BY s.customer_id;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/241/1*24Ur7ys8oyLjqiq3uDDdAQ.jpeg" /></figure><p>2. How many days has each customer visited the restaurant?</p><p>Here, I applied the COUNT DISTINCT function to get the number of days. The COUNT DISTINCT is used to avoid getting the wrong value in case a customer orders multiple menu items on the same day.</p><pre>SELECT customer_id, COUNT(DISTINCT order_date) AS num_of_days<br>FROM sales<br>GROUP BY customer_id;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/269/1*oYVn1lyhK0hwXXBZ-GQ0bQ.jpeg" /></figure><p>3. What was the first item from the menu purchased by each customer?</p><p>To get the first item purchased, I used the DENSE_RANK() window function to rank the purchases for each customer in the CTE called ranked_sales. WHERE rank equals 1, the query then returns all first item purchases for each customer.</p><pre> WITH ranked_sales AS <br> (<br>   SELECT s.customer_id, s.order_date, m.product_name,<br>   DENSE_RANK() OVER(PARTITION BY s.customer_id ORDER BY s.order_date) AS rank<br>   FROM sales AS s<br>   INNER JOIN menu AS m<br>   ON s.product_id = m.product_id<br> )<br><br> SELECT customer_id, product_name<br> FROM ranked_sales<br> WHERE rank = 1<br> GROUP BY customer_id, product_name;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/370/1*EYsUENWHfYgdTLIvcEDTEg.jpeg" /></figure><p>4. What is the most purchased item on the menu and how many times was it purchased by all customers?</p><p>The COUNT function was used to get the total number of purchases by all customers. ORDER BY num_purchases in descending order and TOP 1 returns the top result.</p><pre> SELECT TOP 1 m.product_id, m.product_name, COUNT(product_name) AS num_purchase<br> FROM sales AS s<br> INNER JOIN menu AS m<br> ON s.product_id = m.product_id<br> GROUP BY m.product_id, m.product_name<br> ORDER BY num_purchase DESC;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/653/1*E7S-6Xv8uTQOMMqIS5ff5g.jpeg" /></figure><p>5. Which item was the most popular for each customer?</p><p>In the CTE called most_popular_item, I used the DENSE_RANK() function to rank each customer according to the number of times they purchased a particular menu item. The most popular menu item is then returned for WHERE rank equals 1.</p><pre>WITH most_popular_item AS<br> (<br>   SELECT m.product_name, s.customer_id, COUNT(m.product_id) AS count_rank, <br>   DENSE_RANK() OVER(PARTITION BY s.customer_id ORDER BY COUNT(s.product_id) DESC) AS rank<br>   FROM sales AS s<br>   INNER JOIN menu AS m<br>   ON s.product_id = m.product_id<br>   GROUP BY m.product_name, s.customer_id<br> )<br><br> SELECT customer_id, product_name, count_rank<br> FROM most_popular_item<br> WHERE rank = 1;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/655/1*D2bDBR8lE69sPZN7OvcxtA.jpeg" /></figure><p>6. Which item was purchased first by the customer after they became a member?</p><p>In the CTE, I used a RANK() function to get the rank of purchases by the customers after they became members, joining the three tables.</p><pre>WITH CTE AS<br> (<br>  SELECT m.product_id, product_name, s.customer_id, order_date, join_date,<br>  RANK() OVER(PARTITION BY s.customer_id ORDER BY order_date) AS rank<br>  FROM sales AS s<br>  INNER JOIN menu AS m<br>  ON s.product_id = m.product_id<br>  INNER JOIN members AS mem <br>  ON s.customer_id = mem.customer_id<br>  WHERE order_date &gt; join_date<br> )<br><br> SELECT customer_id, product_name<br> FROM CTE<br> WHERE rank = 1;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/1*JdLD4n2mFnzRMHMG1MMXIg.jpeg" /></figure><p>7. Which item was purchased just before the customer became a member?</p><p>This follows a similar step as the question above. Here, the order_date is less than the join_date.</p><pre>WITH CTE AS<br> (<br>  SELECT m.product_id, product_name, s.customer_id, order_date, join_date,<br>  RANK() OVER(PARTITION BY s.customer_id ORDER BY order_date DESC) AS rank<br>  FROM sales AS s<br>  INNER JOIN menu AS m<br>  ON s.product_id = m.product_id<br>  INNER JOIN members AS mem <br>  ON s.customer_id = mem.customer_id<br>  WHERE order_date &lt; join_date<br> )<br><br> SELECT customer_id, product_name<br> FROM CTE<br> WHERE rank = 1;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/573/1*J-OFd3zAtA6c_plLfps5zA.jpeg" /></figure><p>8. What is the total items and amount spent for each member before they became a member?</p><p>The COUNT function is used to get the total items and the SUM function for the total amount spent, WHERE order_date is less than join_date and GROUP BY customer_id.</p><pre>SELECT s.customer_id AS ID,<br>       COUNT(m.product_id) AS total_items,<br>    CONCAT(&#39;$&#39;, SUM(m.price)) AS total_amt_spent<br>FROM sales s<br>JOIN menu m<br>ON s.product_id = m.product_id<br>JOIN members mem<br>ON s.customer_id = mem.customer_id<br>WHERE s.order_date &lt; mem.join_date<br>GROUP BY s.customer_id;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/816/1*bswGzny_HeeX24U0ct5XEQ.jpeg" /></figure><p>9. If each $1 spent equates to 10 points and sushi has a 2x points multiplier — how many points would each customer have?</p><p>Here, we use the CASE WHEN clause alongside the SUM function to get the points for sushi and for other products.</p><pre>SELECT s.customer_id,<br> SUM(CASE WHEN product_name = &#39;sushi&#39; THEN price * 20<br> ELSE price * 10 END) AS points<br>FROM sales s<br>INNER JOIN menu m<br>ON s.product_id = m.product_id<br>GROUP BY s.customer_id;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/703/1*DayABXpBYHhufhUTKboq3A.jpeg" /></figure><p>10. In the first week after a customer joins the program (including their join date) they earn 2x points on all items, not just sushi — how many points do customers A and B have at the end of January?</p><p>The DATEADD function is used to add 7 days to the join_date to get the date the offer ends. Then we use the SUM and CASE WHEN as in the question above.</p><pre>SELECT s.customer_id,<br>mem.join_date AS offer_start,<br>DATEADD(&quot;day&quot;, 7, mem.join_date) AS offer_end,<br>SUM(CASE <br>        WHEN s.order_date BETWEEN mem.join_date AND DATEADD(&quot;day&quot;, 7  , mem.join_date) THEN price * 20<br>     WHEN product_name = &#39;sushi&#39; THEN price * 20<br>  ELSE price * 10<br>        END) AS points<br>FROM sales s<br>JOIN menu m<br>ON s.product_id = m.product_id<br>JOIN members mem <br>ON s.customer_id = mem.customer_id<br>WHERE MONTH(order_date) = 1  <br>GROUP BY s.customer_id, mem.join_date;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/411/1*qVA-WyCRaUXiEuej2yLOOA.jpeg" /></figure><h3>Bonus Questions</h3><h3>Join All The Things - Recreate the following table output using the available data:</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/601/1*X97_SRId1_xtCKOM0Ovhhw.jpeg" /></figure><pre>SELECT s.customer_id, s.order_date, m.product_name, m.price,<br> CASE WHEN s.order_date &lt; mem.join_date THEN &#39;N&#39;<br>      WHEN s.order_date &gt;= mem.join_date THEN &#39;Y&#39;<br>   ELSE &#39;N&#39;<br>   END AS member<br>INTO recreated_table<br>FROM sales s<br>LEFT JOIN menu m<br>ON s.product_id = m.product_id<br>LEFT JOIN members mem<br>ON s.customer_id = mem.customer_id;<br><br>SELECT *<br>FROM recreated_table</pre><h3>Rank All The Things — Recreate the following table output using the available data:</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/601/1*X97_SRId1_xtCKOM0Ovhhw.jpeg" /></figure><pre>SELECT *,<br> CASE WHEN member = &#39;Y&#39; THEN <br>      RANK() OVER(PARTITION BY customer_id, member ORDER BY order_date)<br>   END AS ranking<br>FROM recreated_table; --the temporary table created above </pre><blockquote><em>Thank you for reading through. Please, feel free to leave comments and share.</em></blockquote><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=cd935d2e4d05" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>