<?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 Mfoniso Jimmy on Medium]]></title>
        <description><![CDATA[Stories by Mfoniso Jimmy on Medium]]></description>
        <link>https://medium.com/@jimmymfoniso2001?source=rss-4a139bd611d0------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/proxy/1*TGH72Nnw24QL3iV9IOm4VA.png</url>
            <title>Stories by Mfoniso Jimmy on Medium</title>
            <link>https://medium.com/@jimmymfoniso2001?source=rss-4a139bd611d0------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Sat, 21 Nov 2026 18:26:07 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@jimmymfoniso2001/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[Survey Platforms]]></title>
            <link>https://medium.com/@jimmymfoniso2001/survey-platforms-583d8855d20f?source=rss-4a139bd611d0------2</link>
            <guid isPermaLink="false">https://medium.com/p/583d8855d20f</guid>
            <category><![CDATA[freelance]]></category>
            <category><![CDATA[earn-money-from-home]]></category>
            <category><![CDATA[earn-money-online]]></category>
            <category><![CDATA[surveys]]></category>
            <dc:creator><![CDATA[Mfoniso Jimmy]]></dc:creator>
            <pubDate>Mon, 04 May 2026 16:15:39 GMT</pubDate>
            <atom:updated>2026-05-05T13:03:39.682Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/256/1*WJoD48ARrf4d_QV5oX7f1A.png" /><figcaption>If you want to try it out, you can sign up here:<br><a href="https://www.paidwork.com/?r=jimmymfoniso2001">https://www.paidwork.com/?r=jimmymfoniso2001</a></figcaption></figure><p><a href="https://www.paidwork.com/?r=jimmymfoniso2001">https://www.paidwork.com/?r=jimmymfoniso2001</a> As a Nigerian who is a Data Analyst, I got engulf to exploring and researching for remote freelancing jobs , this took me on a path of surveys and platforms that pays in dollars. Looking for simple ways to earn online while building towards bigger opportunities, this is worth exploring. I recently started using a platform called PaidWork, and it’s been a practical way to generate some income in my spare time.</p><p>What I like about it is the flexibility. You’re not locked into one type of task ;you can write blog posts (including platforms like Medium), take surveys, complete small online tasks, and even earn while playing games. It gives you options depending on your schedule and what you’re comfortable doing. It has a relatively strong rating (~4.4/5 from many reviews)</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/591/1*4czH1ToNkYwOH0MpTyjkzA.jpeg" /></figure><p>Personally, I’ve been using it to stay productive while still searching for more stable, long-term roles. It’s not a magic solution, but it’s a useful way to stay active, earn something, and build consistency.</p><p>Take your time to explore it and see what works best for you.<a href="https://www.paidwork.com/?r=jimmymfoniso2001">https://www.paidwork.com/?r=jimmymfoniso2001</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=583d8855d20f" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[HR- EMPLOYEE ATTRITION ANALYSIS(EDA)]]></title>
            <link>https://medium.com/@jimmymfoniso2001/hr-employee-attrition-analysis-eda-9256f85f6ad7?source=rss-4a139bd611d0------2</link>
            <guid isPermaLink="false">https://medium.com/p/9256f85f6ad7</guid>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[database]]></category>
            <category><![CDATA[exploratory-data-analysis]]></category>
            <dc:creator><![CDATA[Mfoniso Jimmy]]></dc:creator>
            <pubDate>Tue, 12 Mar 2024 22:37:17 GMT</pubDate>
            <atom:updated>2024-03-12T22:37:17.082Z</atom:updated>
            <content:encoded><![CDATA[<p>Attrition rate is an important HR metric that provides organizations with insight into their workforce’s stability and satisfaction. It also reflects the effectiveness of retention strategies, highlighting potential areas for improvement in workforce planning, employee management, and engagement.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/760/1*qhCI7aZKjIflEfK2_dmA4A.jpeg" /></figure><p>The <a href="https://www.kaggle.com/pavansubhasht/ibm-hr-analytics-attrition-dataset">dataset</a> was obtained from Kaggle, and directly downloaded to my Excel where I converted it to a CSV Flat file to import it to SQL. First I created a database to hold my table “HR-Employee”.</p><p>All Data Cleaning and Data Quality Check was done on Excel.</p><p><strong>Loading Data;</strong></p><pre>use HR_Employee<br><br>Select *<br>from [dbo].[HR-Employee-Attrition Csv]<br></pre><p><strong>Result;</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*k20mEsogYRqvyWW3TrCtpQ.jpeg" /><figcaption>Sample Attrition Table</figcaption></figure><p>Various Business Questions were derived to aid retention strategies, track employees’ profiles, etc. Below are these questions;</p><ol><li>Total Number Of Employees?</li></ol><pre>--Total Number of employees?<br>select count(age) as [Number of employees]<br>from [dbo].[HR-Employee-Attrition Csv]</pre><p>This aimed at knowing the total number of workers in the Company.</p><p><strong>Observation:</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/138/1*VETaUPRjy-5mIejVopY9QA.jpeg" /></figure><p>2. What is the age of the oldest employee?</p><pre>--What is the age of the oldest employee?<br><br>select Max(Age) as Oldest_Employee, Gender<br>from [dbo].[HR-Employee-Attrition Csv]<br>group by Gender</pre><p>This helps the HR identify the oldest of all the employers and aid retirement and other strategies that may aid the smooth run of the company.</p><p><strong>Observation:</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/182/1*O0F2CtVjwHX9GWK0QyV8uw.jpeg" /></figure><p>3. What is the age of the youngest employee?</p><pre>--What is the age of the youngest employee?<br>select Min(Age) as youngest_Employee, Gender<br>from [dbo].[HR-Employee-Attrition Csv]<br>group by Gender </pre><p><strong>Observation:</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/191/1*1qxdX0jo_97phVjUsAn1hw.jpeg" /></figure><p>4. How many Male and Female employees are in the company?</p><pre>--How many male and female employees are in the company?<br><br>select Count(Gender) as Employees, Gender<br>from [dbo].[HR-Employee-Attrition Csv]<br>group by Gender</pre><p>This helps HR know and keep track of their hiring patterns.</p><p><strong>Observations:</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/146/1*PecIjGu7XDelOgZgiibj8w.jpeg" /></figure><p>5. How many employees are in each educational field?</p><pre>--How many employees are in each educational field?<br><br>select count(EmployeeNumber) as Employees, EducationField<br>from [dbo].[HR-Employee-Attrition Csv]<br>group by EducationField<br>Order by Employees desc</pre><p>This gives insight into various educational fields in the company and which might warrant changes.</p><p><strong>Observations:</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/211/1*TNJcYljoGAB1SwuchY8Gtg.jpeg" /></figure><p>6. How many Employees are single and married in the company?</p><pre>--How many Employees are single and married in the company?<br><br>Select count(MaritalStatus) as No_of_Employees, MaritalStatus,Gender<br>from [dbo].[HR-Employee-Attrition Csv]<br>Group by MaritalStatus,Gender</pre><p>This makes sorting based on marital status easier as this will affect their travelling time and duration.</p><p><strong>Observation:</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/272/1*rnZDPTkQaAyUSJKHsKeOBg.jpeg" /></figure><p>7. How Many Sales Executives do we have currently in the company?</p><pre>--How Many Sales Executives do we have currently in the company?<br><br>Select count(EmployeeNumber) sales_exec<br>From [dbo].[HR-Employee-Attrition Csv]<br>Where JobRole =&#39;Sales Executive&#39;</pre><p>This significantly focuses on the sales executive part of the company’s job role</p><p><strong>Observation:</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/91/1*dog5Umd7k-VTFwvngdTp8g.jpeg" /></figure><p>8. Derive the number of Employees who have worked with the company for 10years and above, their role, Age, and Levels.</p><pre>--Derive the number of Employees who have worked with the company for 10years and above,their role,Age, and Levels.<br><br>select count(EmployeeNumber) No_of_Employees<br>from [dbo].[HR-Employee-Attrition Csv]<br>where TotalWorkingYears &gt;= 10<br><br>Select EmployeeNumber,TotalWorkingYears,JobRole,JobLevel,Age<br>from [dbo].[HR-Employee-Attrition Csv]<br>where TotalWorkingYears &gt;= 10</pre><p>First, I wrote a query to just show the total number of employees(using the aggregator “count”) who fell between the selected conditions. Then wrote a second query which carried the other information.</p><p><strong>Observation:</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/127/1*1Dyo9I6AnOBFVUDSneh0vQ.jpeg" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/530/1*VPbj4ThOZ0J-NiP9qb4EcQ.png" /><figcaption>Sample of Employees with working years ≥ 10</figcaption></figure><p>This analysis was done to help the Human Resource Department in the areas of recruitment, retention, support, and management.</p><p>Thanks for reading, feel free to comment and correct any code incase of an error. I’m open to feedback, Learning is a continuous process and it’s time to continue that journey. Thank you</p><p>Feel Free to reach me on <a href="https://www.linkedin.com/in/mfoniso-jimmy-969843227/">Linkedin</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=9256f85f6ad7" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[SQL CHALLENGE 2 : PIZZA RUNNER(DATA WRANGLING AND CLEANING)]]></title>
            <link>https://medium.com/@jimmymfoniso2001/sql-challenge-2-pizza-runner-data-wrangling-and-cleaning-11db12d2f65d?source=rss-4a139bd611d0------2</link>
            <guid isPermaLink="false">https://medium.com/p/11db12d2f65d</guid>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[sql]]></category>
            <dc:creator><![CDATA[Mfoniso Jimmy]]></dc:creator>
            <pubDate>Sat, 24 Feb 2024 16:48:13 GMT</pubDate>
            <atom:updated>2024-02-24T16:48:13.344Z</atom:updated>
            <content:encoded><![CDATA[<h3>SQL CHALLENGE 2 : PIZZA RUNNER(DATA WRANGLING AND CLEANING)</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/851/1*qcMlTvE3zedC3qrl8f3mAA.jpeg" /></figure><p><strong>Introduction</strong></p><p>This is the challenge 2 — <a href="https://8weeksqlchallenge.com/case-study-2/">Pizza Runner</a> of the <a href="https://8weeksqlchallenge.com/getting-started/">8 Weeks SQL Challenge</a> by Danny Ma. I am playing the role of a data analyst at Pizza Runner.</p><p><strong>Problem Statement</strong></p><p>Danny was scrolling through his Instagram feed when something really caught his eye — “80s Retro Styling and Pizza Is The Future!”</p><p>Danny was sold on the idea, but he knew that pizza alone was not going to help him get seed funding to expand his new Pizza Empire — so he had one more genius idea to combine with it — he was going to <em>Uberize</em> it — and so Pizza Runner was launched!</p><p>Danny started by recruiting “runners” to deliver fresh pizza from Pizza Runner Headquarters (otherwise known as Danny’s house) and also maxed out his credit card to pay freelance developers to build a mobile app to accept orders from customers.</p><p>Danny had few years of experience as a data scientist and as such understood data collection and how critical it is to business growth.</p><p>He has prepared for us an entity relationship diagram of his database design but requires further assistance to clean his data and apply some basic calculations so he can better direct his runners and optimise Pizza Runner’s operations.</p><p>All datasets exist within the pizza_runner database schema — be sure to include this reference within your SQL scripts as you start exploring the data and answering the case study questions.</p><p>The Entity relationship Diagram can be seen below</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/945/1*yLf203-sRbfisElE6rtyDA.jpeg" /></figure><p>For <em>runners </em>table</p><p>The runners table shows the registration_date for each new runner</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/357/1*XOjOURpeUlI_3MDmC-x9YQ.jpeg" /></figure><p>For<em> Customer_orders</em> table</p><p>Customer pizza orders are captured in the customer_orders table with 1 row for each individual pizza that is part of the order.</p><p>The pizza_id relates to the type of pizza which was ordered whilst the exclusions are the ingredient_id values which should be removed from the pizza and the extras are the ingredient_id values which need to be added to the pizza.</p><p>Note that customers can order multiple pizzas in a single order with varying exclusions and extras values even if the pizza is the same type!</p><p>The exclusions and extras columns will need to be cleaned up before using them in your queries.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/896/1*XlDesV6xyhJk-toB9ZVBHQ.jpeg" /></figure><p>For <em>runner_orders </em>table</p><p>After each orders are received through the system — they are assigned to a runner — however not all orders are fully completed and can be cancelled by the restaurant or the customer.</p><p>The pickup_time is the timestamp at which the runner arrives at the Pizza Runner headquarters to pick up the freshly cooked pizzas. The distance and duration fields are related to how far and long the runner had to travel to deliver the order to the respective customer.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/912/1*dCwSw6oHRkRDwfSVcfMdww.jpeg" /></figure><p>For <em>pizza_names </em>table</p><p>At the moment — Pizza Runner only has 2 pizzas available the Meat Lovers or Vegetarian!</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/270/1*f1U4mJgjdOU5Wj0pK3gPeQ.jpeg" /></figure><p>For <em>pizza_recipes</em> table</p><p>Each pizza_id has a standard set of toppings which are used as part of the pizza recipe.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/348/1*nGb8LWVOmdPAGn5DI_nRFA.jpeg" /></figure><p>For <em>pizza_toppings</em> table</p><p>This table contains all of the topping_name values with their corresponding topping_id value</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/341/1*Z9zswczSKxYe2yU5kIUgug.jpeg" /></figure><p><strong>Data Wrangling</strong></p><p>Data wrangling has three processes, which are data collection, assessing and cleaning. These steps will be involved in solving this problem.</p><p><strong>Data Gathering</strong></p><p>The data and entity relationships of the case study is found <a href="https://8weeksqlchallenge.com/case-study-2/">https://8weeksqlchallenge.com/case-study-2/.</a></p><p><strong>Data Assessment</strong></p><p>Before we clean the data, it is advisable to go through the datasets to check data qualities and abnormalities. Since the data is not a large data, visual assessment will work just fine.</p><p>This can be done by;</p><pre>  SELECT * FROM customer_orders;<br>  SELECT * FROM pizza_names;<br>  SELECT * FROM pizza_recipes;<br>  SELECT * FROM pizza_toppings;<br>  SELECT * FROM runner_orders;<br>  SELECT * FROM runners;</pre><p>Data quality issues where observed in <em>customer_orders</em>, <em>runner_orders, and pizza_recipes.</em></p><p><strong>Data Cleaning</strong></p><p>After identifying the issues with the tables, this stage is where the data quality issues are dealt with</p><p>For the customer_orders table: Issues observed are</p><ul><li>In the exclusions column, the presence of ‘null’ and ‘ ‘ instead of NULL</li><li>In the extras column, the presence of ‘null’ and ‘ ‘ instead of NULL</li></ul><p>The Code used to correct this is</p><pre>-- for the null and blank values in the exclusions column<br>Update customer_orders<br>set exclusions = case when exclusions = &#39;&#39; then NULL<br>                 when exclusions = &#39;null&#39; then NULL<br>                 else exclusions end;<br>-- for the null and blank values in the extras column<br>UPDATE customer_orders<br>SET extras = CASE WHEN extras = &#39;&#39; THEN NULL <br>             WHEN extras = &#39;null&#39; THEN NULL <br>             ELSE extras END;</pre><p>To test run the codes;</p><pre>select * <br>from customer_orders;</pre><p>Results:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/495/1*Cj3I8MdC7tQiBxNyPFnBWw.jpeg" /></figure><p>For the runner_orders table: Issues observed are</p><ul><li>the pickup_time column has ‘null’ values</li><li>the distance column has the appearance of ‘km’, ‘null’ values and ‘ km’</li><li>the duration column has the appearance of ‘minutes’, ‘null’, ‘mins’, ‘minute’</li><li>the cancellation column has the appearance of ‘null’ and ‘ ‘</li><li>Change the data types of the corrected columns</li></ul><p>The Code to correct these quality issues</p><pre>UPDATE runner_orders<br>SET pickup_time = CASE WHEN pickup_time = &#39;null&#39; THEN NULL <br>                  ELSE pickup_time END;<br>                    <br>UPDATE runner_orders<br>SET distance = CASE WHEN distance = &#39;null&#39; THEN NULL <br>               WHEN distance LIKE &#39;%km&#39; THEN <br>                  TRIM(REPLACE(distance, &#39;km&#39;, &#39;&#39;)) ELSE distance END;<br>            <br>UPDATE runner_orders<br>SET duration = CASE WHEN duration LIKE &#39;%min%&#39; THEN LEFT(duration, 2) <br>               WHEN duration = &#39;null&#39; THEN NULL <br>               ELSE duration END ;<br>            <br>UPDATE runner_orders<br>SET cancellation = CASE WHEN cancellation IN (&#39;null&#39;, &#39;&#39;) THEN NULL <br>                   ELSE cancellation END;</pre><p>Results:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/537/1*TQhDG0MDQtQpOmJfDVYXOA.jpeg" /></figure><p>After this, you’ll need to check the datatypes of each columns</p><pre>describe table runner_orders </pre><p>Result:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/412/1*mqVyI4pOGpL5s26-H_uVlg.jpeg" /></figure><p>To change the data types of the pickup_time , distance &amp; duration</p><pre>-- To change the datatype of the columns<br>ALTER TABLE runner_orders<br>MODIFY COLUMN pickup_time DATETIME;<br><br>ALTER TABLE runner_orders<br>MODIFY COLUMN distance Float;<br><br>ALTER TABLE runner_orders<br>MODIFY COLUMN duration INT;</pre><p>Results:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/310/1*SY5Axzyxmg1LwrbTQtaQ0g.jpeg" /></figure><p>This Ends the Data cleaning requirements for this challenge, but there is further voluntary cleaning one can do in the <em>customer_orders</em> and <em>pizza_recipes</em> tables.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=11db12d2f65d" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[SQL CHALLENGE 2 : PIZZA RUNNER ( EXPLORATORY DATA ANALYSIS)]]></title>
            <link>https://medium.com/@jimmymfoniso2001/sql-challenge-2-pizza-runner-exploratory-data-analysis-cdd62e01d3a4?source=rss-4a139bd611d0------2</link>
            <guid isPermaLink="false">https://medium.com/p/cdd62e01d3a4</guid>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[sql]]></category>
            <dc:creator><![CDATA[Mfoniso Jimmy]]></dc:creator>
            <pubDate>Sat, 24 Feb 2024 16:46:25 GMT</pubDate>
            <atom:updated>2024-02-24T16:46:25.432Z</atom:updated>
            <content:encoded><![CDATA[<p><strong>SQL CHALLENGE 2 : PIZZA RUNNER ( EXPLORATORY DATA ANALYSIS)</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/851/1*qcMlTvE3zedC3qrl8f3mAA.jpeg" /></figure><p><strong>Introduction</strong></p><p>This is the challenge 2 —<a href="https://8weeksqlchallenge.com/case-study-4/"> Pizza Runner </a>of the<a href="https://8weeksqlchallenge.com/getting-started/"> 8 weeks SQL Challenge</a> by Danny Ma. I am taking up the role of a data analyst at Pizza Runner.</p><p>This Week 2 task encompasses both Data Cleaning and Exploratory Data Analysis.</p><p>The Data cleaning phase of the work has been done and well documented in this article, do well to read that before reading this as it will aid understanding.</p><p>The <strong>Exploratory Data Analysis (EDA)</strong> would be documented in the post. The Full Documentation of this process can be found in my GitHub</p><p><strong>Exploratory Data Analysis</strong></p><p>To begin this stage, I would like the share the Entity relationship Diagram of the tables in the <em>pizza_runner</em> database</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/762/1*a-iR28OnFGvmMQuP66qSoQ.jpeg" /></figure><p>For the <em>runners</em> table;</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/319/1*tqq4C-migBLE2A_vtJ6gWw.jpeg" /></figure><p>The <em>runners </em>table shows the <em>registration_date</em> for each new runner</p><p>For the <em>customer_orders </em>table;</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/580/1*auxZGmqyxbbqHbkYseGwWg.jpeg" /></figure><p>Customer pizza orders are reflected in the <em>customer_orders </em>table with a row for each individual pizza that is part of the order.</p><p>The <em>pizza_id</em> relates to the type of pizza that was ordered while the <em>exclusions </em>are the ingredient_id values which should be removed from the pizza and the <em>extras </em>are the ingredient_id values which need to be added to the pizza.</p><p>Please Note that customers can order multiple pizzas in a single order with different exclusions and extras values even if the pizza is the same type!</p><p>The <em>exclusions </em>and <em>extras</em> columns will need to be cleaned up before using them in your queries.</p><p>For the <em>runner_orders</em> table;</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/592/1*_UnXdUNOgyadc7TrKHLIeg.jpeg" /></figure><p>After each orders are received through the system, they are assigned to a runner. However some orders can be cancelled by the restaurant or the customer and as such not completed.</p><p>The <em>pickup_time</em> is the datetime at which the runner arrives at the Pizza Runner headquarters to pick up the freshly baked pizzas. The <em>distance</em> and <em>duration</em> fields are related to how far and long the runner had to travel to deliver the order to the respective customer.</p><p>For the <em>pizza_names</em> table;</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/203/1*-xXKiSbnGSCWOxymjk6hkQ.jpeg" /></figure><p>The Pizza Runner at this moment only has 2 types of pizza; Meat Lovers and vegetarian.</p><p>For the <em>pizza_recipes</em> table;</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/256/1*ZxpJt05VLO47uqPfnLTwiA.jpeg" /></figure><p>Each <em>pizza_id</em> has a standard set of toppings which are used as part of the pizza recipe.</p><p>For the <em>pizza_toppings</em> table;</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/241/1*xEnlrkSlxhAjTbIASCkgMw.jpeg" /></figure><p>This table contains all the <em>topping_name</em> with their corresponding <em>topping_id </em>value.</p><p><strong>Case Study Questions</strong></p><p>The following Case Study Questions were asked of this database and solutions were provided using MS SQL.</p><ol><li>How many pizzas were ordered?</li></ol><pre>--How many pizzas were ordered?<br><br>select COUNT(pizza_id) as no_of_pizza_ordered<br>from customer_orders;</pre><p>This question is aimed at knowing the number of pizza ordered by the customers. The Count function is used to count the rows from the <em>customer_order </em>table to know the number of pizza ordered.</p><p>Output;</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/140/1*COG2e1D0nVSkwK9eHcv2pg.jpeg" /></figure><p>2. How many unique customer orders were made?</p><pre>--How many unique customer orders were made?<br><br>select count(distinct(order_id)) as unique_order<br>from customer_orders;</pre><p>This has to do with distinctly counting orders made by customers. this knowledge excludes duplicates.</p><p>Output;</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/98/1*gRrLwyhnh-eMCWM97Q8f_w.jpeg" /></figure><p>3. How many successful orders were delivered by each runner?</p><pre>-- How many successful orders were delivered by each runner?<br><br>select runner_id,count(order_id) as successful_order<br>from runner_orders<br>where cancellation is null <br>group by runner_id;</pre><p>Successful Orders in this case are orders with <em>cancellation</em> column being null. This means the orders where not canceled.</p><p>Output;</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/183/1*BmOBblpEQc5ZO7ZCa4lciA.jpeg" /></figure><p>4. How many of each type of pizza was delivered?</p><pre>-- How many of each type of pizza was delivered?<br><br>select count(co.pizza_id) as pizza_delivered,pn.pizza_id<br>from customer_orders co<br>join pizza_names pn<br>on co.pizza_id = pn.pizza_id<br>join runner_orders ro<br>on co.order_id =ro.order_id<br>where cancellation is null <br>group by pn.pizza_id</pre><p>This sets to answer how many of each pizza type was delivered. The Group By function was included as a result of the aggregator <em>Count</em>.</p><p>Output;</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/167/1*uTHx79lKZl2nFIpCJVrePg.jpeg" /></figure><p>5. How many Vegetarian and Meatlovers were ordered by each customer?</p><pre>-- How many Vegetarian and Meatlovers were ordered by each customer?<br>select customer_id, count(case when pn.pizza_name  = &#39;meatlover&#39; <br>                         then pizza_name end) as count_of_meatlover_pizza_orders,<br>         count(case when pn.pizza_name =&#39;vegetarian&#39;<br>           then pizza_name end) as count_of_vegetarian_pizza_orders<br>from customer_orders co<br>join pizza_names pn<br>on co.pizza_id = pn.pizza_id<br>group by customer_id<br>order by customer_id;</pre><p>In this case, using the count case helps count the pizza names. There are other ways to go about this, if it has a different data type using <em>count case when cast </em>will be used<em>.</em></p><p>Output;</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/560/1*cxKUsLBQAaWMx9c9CFtmbA.jpeg" /></figure><p>6. What was the maximum number of pizzas delivered in a single order?</p><pre>-- What was the maximum number of pizzas delivered in a single order?<br><br>select max(num_of_pizza) as max_pizza<br>from(<br>select co.order_id, count(pizza_id) as num_of_pizza <br>from customer_orders co<br>join runner_orders ro<br>on co.order_id = ro.order_id<br>where cancellation is null<br>group by co.order_id) as no_of_orders</pre><p>In this problem, I used subquery to first count the number of orders and then the aggregator ‘Max’ to get the maximum number of delivered pizza.</p><p>Output;</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/78/1*MLRZHOCqoX_US6yTcPzGAg.jpeg" /></figure><p>7. For each customer, how many delivered pizzas had at least 1 change and how many had no changes?</p><pre><br>--For each customer, how many delivered pizzas had at least 1 change, and how many had no changes?<br>select customer_id,count(case when exclusions is not null or extras is not null then 1 end) as delivered_pizza_with_atleast_one_change, <br>                   COUNT(case when exclusions is null and extras is null then 1 end) as delivered_pizza_with_no_change<br>from customer_orders co<br>join runner_orders ro<br>on co.order_id = ro.order_id<br>where cancellation is null<br>group by customer_id;</pre><p>Exclusions and Extras in this case are the changes made to various orders.</p><p>Output;</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/543/1*xbjSOG8O3g5x3tyNMnrYhw.jpeg" /></figure><p>8. How many pizzas were delivered that had both exclusions and extras?</p><pre><br>-- How many pizzas were delivered that had both exclusions and extras?<br>select ro.order_id,count(exclusions) as exclusions, COUNT(extras) as extras<br>from customer_orders co<br>join runner_orders ro<br>on co.order_id = ro.order_id<br>where exclusions is not null and extras is not null and cancellation is null<br>group by ro.order_id;</pre><p>Since this question is set to find the pizza with both exclusions and extras, I used the where clause to depict the condition.</p><p>Output;</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/188/1*Fw4JXmFMXsUHZFZw9xJh5Q.jpeg" /></figure><p>9. What was the total volume of pizzas ordered for each hour of the day?</p><pre>What was the total volume of pizzas ordered for each hour of the day?<br><br>select Datepart(hour, order_time) as hour_of_the_day, count(pizza_id) as vol_of_pizza<br>from customer_orders<br>group by Datepart(hour, order_time)</pre><p>Using the Datepart function to extract the hour from <em>order_time</em> which contains the timing of the pizza process. The count of pizza is the volume of pizza and the group by is used in this case because of the aggregator in the select statement.</p><p>Output;</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/196/1*3rXJiKWlrvBmPEaggErdYQ.jpeg" /></figure><p>10. What was the volume of orders for each day of the week?</p><pre>-- What was the volume of orders for each day of the week?<br><br>select day(order_time) as day_of_week,datename(WEEKDAY,order_time) as day_name,count(pizza_id) as vol_pizza<br>from customer_orders<br>group by day(order_time),datename(weekday,order_time)<br>order by day(order_time) , datename(weekday,order_time)</pre><p>The Day function is used to extract the days, datename is used to reflect the names of the weekdays for clarity.</p><p>Output;</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/245/1*dG5fDs4EiwdVHQRvD-f6Ug.jpeg" /></figure><p>There are lots of question in this case study and there are neatly documented in my GitHub <a href="https://github.com/Mexxajimmy/Danny-SqL-Challenge/tree/main/DannyChallenge/Week%202%20Challenge">repository</a>.</p><p>Thanks for reading, feel free to comment and correct any code incase of an error. I’m open to feedback, Learning is a continuous process and it’s time to continue that journey. Thank you</p><p>Feel Free to reach me on <a href="https://www.linkedin.com/in/mfoniso-jimmy-969843227/">Linkedin</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=cdd62e01d3a4" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>