<?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 Victor Somadina on Medium]]></title>
        <description><![CDATA[Stories by Victor Somadina on Medium]]></description>
        <link>https://medium.com/@victorSom.?source=rss-930c9ea6a819------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*Ehu2RWA9ME5uEJFd8Rmpzg.jpeg</url>
            <title>Stories by Victor Somadina on Medium</title>
            <link>https://medium.com/@victorSom.?source=rss-930c9ea6a819------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Fri, 20 Nov 2026 08:47:45 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@victorSom./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[SQL Danny Ma Week 2 Challenge: Pizza Runner Solution]]></title>
            <link>https://medium.com/@victorSom./sql-danny-ma-week-2-challenge-pizza-runner-solution-dba67eeaf2c9?source=rss-930c9ea6a819------2</link>
            <guid isPermaLink="false">https://medium.com/p/dba67eeaf2c9</guid>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[sql]]></category>
            <dc:creator><![CDATA[Victor Somadina]]></dc:creator>
            <pubDate>Fri, 24 Mar 2023 13:14:19 GMT</pubDate>
            <atom:updated>2024-05-09T09:51:56.100Z</atom:updated>
            <content:encoded><![CDATA[<p>Unlike week 1, the dataset for this challenge requires cleaning and solving a lot of questions to help the start-up business (pizza runner) grow. Sit tight as I take you through the solutions of this project starting with the data cleaning process to solving the business problems.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*_qg3jgz0W4aJ8AQEFyNesw.png" /></figure><p><strong>Data Cleaning Process</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/861/1*QZASAnFgrNh326tRsMIlhA.jpeg" /><figcaption>Entity Relationship Diagram</figcaption></figure><p>After loading my tables to my database, I did a proper assessment to figure out the major issues with the <strong>runners_orders,</strong> <strong>customers_orders</strong> and <strong>Pizza_recipe </strong>tables, so we are going to identify the issues together and solve them together starting with the customer_orders table.</p><p>Remember that making a copy of your dataset before cleaning is very vital. Don&#39;t forget that!</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/220/1*BLRMVAZvMRHec9T3RF-fyA.gif" /></figure><p>Let’s GOOOO!!!</p><p><strong>Cleaning customer_orders table</strong>: This table contains values alongside empty values, nulls as a string value, and then nulls. We will make this consistent by replacing empty strings and null strings with an actual null.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*-iByi6qUbYufl-X7EGK9mQ.png" /><figcaption>SQL query to make the exclusion and extras column consistent.</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/427/1*vVNJQng__QjOTeKA5ifofQ.jpeg" /><figcaption>Problem solved.</figcaption></figure><p><strong>Note:</strong> Looking at the customer orders table, you will notice the columns having two values separated by a comma, this is a data issue that requires cleaning, but we will get back to that.</p><p><strong>Cleaning runner_orders table: </strong>Looking at the table below, you will notice a lot of data issues including: distance and duration column having inconsistent data entries, presence of null values, incorrect datatypes.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/524/1*Ee3iFoLnd_mpnsjSbLnm_w.jpeg" /><figcaption>runner_orders table</figcaption></figure><p>First, I replaced ‘null’ strings and empty cells with null values to make the data consistent.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*q1hR-i1B5E1hQ5BFSgkFpQ.png" /><figcaption>SQL query to deal to with the inconsistent null values.</figcaption></figure><p>Secondly, I removed the ‘km, mins, and minutes ’ inconsistency in the distance and duration column.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Eh0sAHlhi0cYaSulTbi7zQ.png" /><figcaption>query to deal with the above problem.</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/513/1*7MlIwQHk0oWiwJqkWsvhMg.jpeg" /><figcaption>A peep at what the table looks like after cleaning.</figcaption></figure><p><strong>Cleaning Pizza_recipe table: </strong>This table simply needs to be unpivoted to eliminate the commas. This is a data structure issue, and should be cleaned appropriately.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/202/1*RsThtuW6M056eq1_ds-0eg.jpeg" /><figcaption>pizza recipe table.</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*v6YJQZEI2vq4PN44zS4wdg.png" /><figcaption>SQL query to unpivot the p_toppings column.</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/152/1*TMDgoJtxrdM07U17F6ZFmA.jpeg" /><figcaption>problem solved.</figcaption></figure><p>oh WOW!!! congratulation for coming this far.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/800/1*1KaYXoso-xegR21dGvJA7A.gif" /></figure><p>Now, let’s go into the problem-solving part. Stick with me in this journey.</p><p><strong>Exploratory Data Analysis (Problem Solving)</strong></p><p><strong>Pizza Metrics</strong></p><p>Q1: How many pizzas were ordered?</p><pre>SELECT <br>    COUNT(order_id) as number_of_orders<br>FROM<br>    customer_orders_copy;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/166/1*_mBVC1RK8Xfutmk0KsGX-w.png" /></figure><p>Q2: How many unique customer orders were made?</p><pre>SELECT <br>    COUNT(DISTINCT (order_id)) as Total_customer_orders<br>FROM<br>    customer_orders_copy;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/175/1*b96pRpmJT2tYLOi1yjL5SQ.png" /></figure><p>Q3: How many successful orders were delivered by each runner?</p><pre>SELECT <br>    runner_id, COUNT(order_id) AS Number_of_successful_orders<br>FROM<br>    runner_orders_copy<br>GROUP BY runner_id;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/265/1*eYIrYpH-wjgDfdDuutXxuw.png" /></figure><p>Q4: How many of each type of pizza was delivered?</p><pre>SELECT <br>    pn.pizza_name, COUNT(rc.order_id) AS pizza_delivered<br>FROM<br>    runner_orders_copy rc<br>        JOIN<br>    customer_orders_copy c ON c.order_id = rc.order_id<br>        JOIN<br>    pizza_names pn ON pn.pizza_id = c.pizza_id<br>GROUP BY pn.pizza_name;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/201/1*C7c7nKb4z_zKbQ2SBIVm9g.jpeg" /></figure><p>Q5: How many Vegetarians and Meatlovers were ordered by each customer?</p><pre>SELECT <br>    c.customer_id AS customers,<br>    pn.pizza_name,<br>    COUNT(c.pizza_id) AS pizza_delivered<br>FROM<br>    customer_orders_copy c<br>        JOIN<br>    pizza_names pn ON c.pizza_id = pn.pizza_id<br>GROUP BY c.customer_id , pn.pizza_name<br>ORDER BY c.customer_id;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/264/1*8QJ5FoTGrG_RAJVF8t0w3g.jpeg" /><figcaption>Answer</figcaption></figure><p>Q6: What was the maximum number of pizzas delivered in a single order?</p><pre>SELECT <br>    customer_id,<br>    order_id,<br>    COUNT(order_id) AS number_of_pizzas_delivered<br>FROM<br>    customer_orders_copy<br>GROUP BY customer_id , order_id<br>ORDER BY COUNT(order_id) DESC<br>LIMIT 1;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/331/1*Pou4CN08-xLgjAFubjMN6w.jpeg" /></figure><p>Q7: For each customer, how many delivered pizzas had at least 1 change and how many had no changes?</p><pre>/* when we talk about having change, we talk about whether the customer <br>have exclusion or extras and whether he doesnt*/<br><br>SELECT <br>    customer_id,<br>    COUNT(rc.order_id) AS delivered_pizzas,<br>    SUM(CASE<br>        WHEN<br>            (exclusions IS NOT NULL<br>                OR extras IS NOT NULL)<br>        THEN<br>            1<br>        ELSE 0<br>    END) AS pizza_with_changes,<br>    SUM(CASE<br>        WHEN (exclusions IS NULL AND extras IS NULL) THEN 1<br>        ELSE 0<br>    END) AS pizza_without_changes<br>FROM<br>    customer_orders_copy c<br>        JOIN<br>    runner_orders_copy rc ON c.order_id = rc.order_id<br>GROUP BY customer_id;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/453/1*G2iZzRsTdwl6VGGuz2zcfw.jpeg" /><figcaption>answer to the query above.</figcaption></figure><p>Q8: How many pizzas were delivered that had both exclusions and extras?</p><pre>-- This is for both exclusions and extras<br>SELECT <br>    customer_id,<br>    COUNT(rc.order_id) AS delivered_pizzas,<br>    SUM(CASE<br>        WHEN<br>            (exclusions IS NOT NULL<br>                AND extras IS NOT NULL)<br>        THEN<br>            1<br>        ELSE 0<br>    END) AS pizza_with_changes<br>FROM<br>    customer_orders_copy c<br>        JOIN<br>    runner_orders_copy rc ON c.order_id = rc.order_id<br>GROUP BY customer_id;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/323/1*ngNOYzclrJaV32YgCwdASQ.jpeg" /><figcaption>customers 103 and 104 had Pizzas where pizza ingredients were added and excluded.</figcaption></figure><p>Q9: What was the total volume of pizzas ordered for each hour of the day?</p><p>This question can be interpreted as number of pizzas ordered at each hour, for e.g., at 11:00, how many pizzas were ordered at 12:00, how many pizzas were ordered and so on.</p><pre>SELECT <br>    HOUR(order_time) AS &#39;Hour&#39;,<br>    COUNT(order_id) AS qty_of_pizza_ordered<br>FROM<br>    customer_orders_copy<br>GROUP BY HOUR(order_time)<br>ORDER BY HOUR(order_time)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/204/1*-yicX7ZdIQQRvkNAUnGv6w.jpeg" /></figure><p>Q10: What was the volume of orders for each day of the week?</p><pre>SELECT <br>    DAYNAME(date(order_time)) AS day_of_week,<br>    COUNT(order_id) AS qty_of_pizza_ordered<br>FROM<br>    customer_orders_copy<br>GROUP BY day_of_week<br>ORDER BY qty_of_pizza_ordered DESC;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/244/1*pXbNBJyoJ1RSFkb0NY0x4w.jpeg" /></figure><p>This brings us to the end of section A, unto to the next section. keep Going Buddy!</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/640/1*omtp7DCnrrFW-HbatyN20Q.gif" /></figure><h3>Runner and Customer Experience</h3><p>Q1: How many runners signed up for each 1-week period? (i.e., week starts 2021-01-01)?</p><pre>SELECT <br>    CASE<br>        WHEN WEEK(Registration_date) = 0 THEN &#39;First_week&#39;<br>        WHEN WEEK(Registration_date) = 1 THEN &#39;Second_week&#39;<br>        WHEN WEEK(Registration_date) = 2 THEN &#39;Third_week&#39;<br>        ELSE WEEK(Registration_date)<br>    END AS a_week_period,<br>    COUNT(Runner_id) AS registered_runners<br>FROM<br>    runners<br>GROUP BY a_week_period;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/238/1*RVOMlSXzECynB3P2J8MC8A.jpeg" /></figure><p>Q2: What was the average time in minutes it took for each runner to arrive at the Pizza Runner HQ to pick up the order?</p><pre>SELECT <br>    runner_id,<br>    ROUND(AVG(TIMESTAMPDIFF(MINUTE,<br>                order_time,<br>                pickup_time)),<br>            2) AS average_time_in_minutes<br>FROM<br>    runner_orders rc<br>        JOIN<br>    customer_orders_copy c ON c.order_id = rc.order_id<br>GROUP BY runner_id;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/238/1*rIF_kQKZ91pTIGhLCW45Fg.jpeg" /></figure><p>Q3: Is there any relationship between the number of pizzas and how long the order takes to prepare?</p><pre>SELECT <br>    customer_id,<br>    COUNT(c.order_id) AS Number_of_pizza,<br>    ROUND(AVG(TIMESTAMPDIFF(MINUTE,<br>                order_time,<br>                pickup_time)),<br>            2) AS average_time_in_minutes<br>FROM<br>    customer_orders_copy c<br>        JOIN<br>    runner_orders rc ON rc.order_id = c.order_id<br>GROUP BY customer_id<br>ORDER BY Number_of_pizza DESC;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/350/1*AfDzPaCKFfK4eWlj5UUHHw.jpeg" /></figure><p>It appears that as the number of pizzas increases, average time taken to prepare also increase. Statistically, this can be tagged a positive correlation between number of pizzas and average time to prepare.</p><p>Q4: What was the average distance travelled for each customer?</p><pre>SELECT <br>    customer_id, ROUND(AVG(distance), 2) AS avg_distance<br>FROM<br>    customer_orders_copy c<br>        JOIN<br>    runner_orders_copy rc ON c.order_id = rc.order_id<br>GROUP BY customer_id;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/194/1*pqUv2LYQ6p3yNiU0HkxEcA.jpeg" /></figure><p>Q5: What was the difference between the longest and shortest delivery times for all orders?</p><pre>SELECT <br>    MAX(duration) - MIN(duration) AS difference_in_duration_for_all_orders<br>FROM<br>    runner_orders_copy;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/241/1*nlsH4GJBMLL1Kqz0lDO_Jg.jpeg" /></figure><p>Q6: What was the average speed for each runner for each delivery and do you notice any trend for these values?</p><pre>-- speed is calculated in km/hr. Time_duration should be converted to hour by dividing by 60<br><br>SELECT <br>    r.Runner_id,<br>    ROUND(duration / 60, 2) AS time_duration_hr,<br>    ROUND(AVG(distance / ROUND(duration / 60, 2)),<br>            2) AS avg_speed<br>FROM<br>    runner_orders_copy ro<br>        JOIN<br>    runners r ON ro.runner_id = r.Runner_id<br>WHERE<br>    duration IS NOT NULL<br>GROUP BY r.Runner_id , time_duration_hr<br>ORDER BY r.Runner_id;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/278/1*9yTpSbSAasAGD9bkQW4jcQ.jpeg" /></figure><p>From the result above, you can notice that the speed of the individual runners decreases per order.</p><p>Q7: What is the successful delivery percentage for each runner?</p><pre>SELECT <br>    runner_id,<br>    COUNT(*) AS total_orders,<br>    ROUND((COUNT(pickup_time) / COUNT(*)) * 100, 0) AS delivery_percentage<br>FROM<br>    runner_orders_copy<br>GROUP BY runner_id; </pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/291/1*1T5FYZ9iTrzJo_i70af9zw.jpeg" /></figure><p>Hurray! Now we move down to the next section.</p><h3>Ingredient Optimization</h3><p>Q1: What are the standard ingredients for each pizza?</p><pre>with cte1 as(<br>              select pn.pizza_name, topping_name <br>              from pizza_toppings pt join pizza_recipe_copy pr on pt.topping_id=pr.p_toppings<br>              join pizza_names pn on pn.pizza_id = pr.pizza_id<br>              order by pn.pizza_name)<br>select pizza_name, group_concat(topping_name separator &#39;,  &#39;) as topping_name from cte1<br>group by pizza_name;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/363/1*FyRJRJU3IlnuBOeXbsRwvw.jpeg" /></figure><p>Q2: What was the most commonly added extra?</p><pre>-- I created a temporary table that unpivots the comma separated values in the extras column<br><br>drop table if exists extras_break;<br>Create temporary table extras_break as<br>select record_id, trim(substring_index(substring_index(extras, &#39;,&#39;, n), &#39;,&#39;, -1)) as extras<br>from customer_orders_copy<br>cross join ( select 1 as n<br>           union all <br>     select 2 <br>            union all select 3) as numbers<br>where n&lt;=1 + length(extras)-length(replace(extras, &#39;,&#39;, &#39;&#39;));<br><br>-- After which I counted the extras to get the most commonly added extras<br><br>select topping_name, count(extras) as number_of_extras from extras_break eb <br>join pizza_toppings pt on pt.topping_id = eb.extras<br>group by topping_name <br>order by count(extras) desc<br>limit 1;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/152/1*OyygZrb2VnGlXtCpaqD0GA.jpeg" /><figcaption>Temporary table to unpivot the extras column</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/232/1*t2SGy0etoYTl5JcxwCwOtg.jpeg" /><figcaption>most commonly added extras.</figcaption></figure><p>Q3: What was the most common exclusion?</p><pre>-- I created a temporary table to unpivot the exclusion column.<br><br>drop table if exists exclusion_break;<br>Create temporary table exclusion_break as<br>select record_id, trim( substring_index(substring_index(exclusions, &#39;,&#39;,   n), &#39;,&#39;, -1)) as exclusions<br>from customer_orders_copy <br>cross join ( select 1 as n<br>             union all <br>             select 2 <br>             union all select 3) as numbers<br>where n&lt;=1 + length(exclusions)-length(replace(exclusions, &#39;,&#39;, &#39;&#39;));<br><br>-- Did count and limit 1 to get the most commonly excluded ingredient<br><br>select topping_name, count(exclusions) as number_of_exclusions from exclusion_break eb <br>join pizza_toppings pt on pt.topping_id = eb.exclusions<br>group by topping_name <br>order by count(exclusions) desc<br>limit 1;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/171/1*gtttGD28Redhf12_SFiI4Q.jpeg" /><figcaption>unpivoted exclusion column</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/249/1*S_MVkmgQjK_wxwhSxvIdNQ.jpeg" /><figcaption>most commonly excluded ingredient.</figcaption></figure><p>Q4: Generate an order item for each record in the customers_orders table in the format of one of the following:</p><ul><li>Meat Lovers</li><li>Meat Lovers - Exclude Beef</li><li>Meat Lovers - Extra Bacon</li><li>Meat Lovers - Exclude Cheese, Bacon - Extra Mushroom, Peppers</li></ul><pre>-- I created 3 cte&#39;s. This is to select toppings and concat toppings have extra ingredient or excluded ingredients with &#39;extra&#39; and &#39;exclude&#39;<br>with cteextras as (<br>select e.record_id, concat(&#39;Extra &#39;, group_concat(t.topping_name separator &#39;, &#39;)) as record_options<br>from extras_break e <br>join pizza_toppings t on e.extras = t.topping_id<br>group by e.record_id), <br>cteexclusion as (<br>select e.record_id, concat(&#39;Exclude &#39;, group_concat(t.topping_name separator &#39;, &#39;)) as record_options<br>from exclusion_break e <br>join pizza_toppings t on e.exclusions = t.topping_id<br>group by e.record_id),<br>cteunion as (<br>select * from cteextras<br>union all<br>select * from cteexclusion)<br><br>-- The code below concat pizza name with the toppings generated in the previous code with a separator &#39;-&#39;<br><br>select c.record_id, c.order_id, c.customer_id, c.pizza_id, c.order_time, <br>concat_ws(&#39; - &#39;, p.pizza_name, group_concat(u.record_options)) as order_item from customer_orders_copy c<br>left join cteunion u on c.record_id = u.record_id<br>join pizza_names p on c.pizza_id = p.pizza_id<br>group by c.record_id, c.order_id, c.customer_id, c.pizza_id, c.order_time, p.pizza_name<br>order by record_id;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/557/1*BXzwWp9Br-uJdqXIcyLtPw.jpeg" /></figure><p>Q5: Generate an alphabetically ordered comma separated ingredient list for each pizza order from the customer_orders table and add a 2x in front of any relevant ingredients.</p><pre>with ingredient as (<br>select c.record_id, c.order_id, c.customer_id, c.pizza_id, c.order_time, p.pizza_name,<br>case when t.topping_id in (select e.extras from extras_break e where e.record_id = c.record_id) then <br>concat(&#39;2x &#39;, t.topping_name) else t.topping_name end as topping, <br>case when t.topping_id in (select ex.exclusions from exclusion_break ex where ex.record_id = c.record_id) then <br>replace(t.topping_name, t.topping_name, &#39;&#39;) else topping_name end ex_topping<br>from customer_orders_copy c <br>join pizza_names p on c.pizza_id = p.pizza_id<br>join pizza_recipe_copy pr on c.pizza_id = pr.pizza_id <br>join pizza_toppings t on pr.p_toppings = t.topping_id)<br><br>select record_id, pizza_id, order_id, customer_id,<br>concat(pizza_name, &#39;: &#39;, group_concat(topping order by topping separator &#39;, &#39; )) as ingredient_list<br>from ingredient<br>group by record_id, order_id, customer_id, pizza_id, order_time, pizza_name</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/751/1*QjSPRxnJ_y4J0chCmDuMIw.png" /></figure><p>Q6: What is the total quantity of each ingredient used in all delivered pizzas sorted by most frequent first?</p><pre>-- when extras then 2, when exclude then 0, when nothing is added or excluded, we are dealing with just 1 ingredient<br>SELECT <br>    topping_name, SUM(times_used) AS num_of_times_used<br>FROM<br>    (SELECT <br>        c.record_id,<br>            pt.topping_name,<br>            CASE<br>                WHEN<br>                    pr.p_toppings IN (SELECT <br>                            extras<br>                        FROM<br>                            extras_break e<br>                        WHERE<br>                            e.record_id = c.record_id)<br>                THEN<br>                    2<br>                WHEN<br>                    pr.p_toppings IN (SELECT <br>                            exclusions<br>                        FROM<br>                            exclusion_break e<br>                        WHERE<br>                            c.record_id = e.record_id)<br>                THEN<br>                    0<br>                ELSE 1<br>            END AS times_used<br>    FROM<br>        customer_orders_copy c<br>    JOIN pizza_recipe_copy pr ON pr.pizza_id = c.pizza_id<br>    JOIN pizza_toppings pt ON pt.topping_id = pr.p_toppings<br>    JOIN pizza_names p ON p.pizza_id = c.pizza_id) AS ingredients<br>GROUP BY topping_name<br>ORDER BY SUM(times_used) DESC;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/230/1*wSiwh4J-tUCmXjWaiNEaHw.jpeg" /></figure><h3>Pricing and Ratings</h3><p>Q1: If a Meat Lovers pizza costs $12 and Vegetarian costs $10 and there were no charges for changes — how much money has Pizza Runner made so far if there are no delivery fees?</p><pre>SELECT <br>    CONCAT(&#39;$ &#39;,<br>            SUM(CASE<br>                WHEN c.pizza_id = 1 THEN 12<br>                ELSE 10<br>            END)) AS Revenue<br>FROM<br>    customer_orders_copy AS c<br>        JOIN<br>    runner_orders_copy AS r ON c.order_id = r.order_id<br>WHERE<br>    r.distance IS NOT NULL;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/94/1*JEYXkqp7Ukt9Gmm2ELpIiQ.png" /><figcaption>$ 138 revenue generated.</figcaption></figure><p>Q2: What if there was an additional $1 charge for any pizza extras?</p><ul><li>Add cheese is $1 extra.</li></ul><pre>with cte_pizza_prize  as (<br>select sum(prize_of_pizza) as pizza_prize <br>from (<br>       select<br>    case when pizza_id = 1 then 12 else 10 end as prize_of_pizza<br>       from customer_orders_copy c join runner_orders_copy r on c.order_id = r.order_id<br>       where r.distance is not null<br>    ) as prices),<br>cte_extra as (<br>select sum(extra_charge) as charge <br>from (<br>   select case when eb.extras = 4 then 2 else 1 end as extra_charge<br>       from extras_break eb join customer_orders_copy c on eb.record_id = c.record_id<br>       join runner_orders_copy r on c.order_id = r.order_id<br>       where r.distance is not null) as charges)<br>select concat(&#39;$ &#39;, cte_pizza_prize.pizza_prize + cte_extra.charge)  as Revenue_charges from cte_pizza_prize, cte_extra;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/144/1*M7FEcy3Di44KLK5eCHlipw.png" /><figcaption>revenue when extra $1 is added to cheese</figcaption></figure><p>Q3: The Pizza Runner team now wants to add an additional ratings system that allows customers to rate their runner, how would you design an additional table for this new dataset — generate a schema for this new table and insert your own data for ratings for each successful customer order between 1 to 5.</p><pre>drop table if exists runner_rating;<br>create table runner_rating (<br>order_id int,<br>rating int);<br><br>insert into runner_rating (order_id, rating)<br>values<br>  (1,3),<br>  (2,5),<br>  (3,3),<br>  (4,1),<br>  (5,5),<br>  (7,3),<br>  (8,4),<br>  (10,3);</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/144/1*gcA1RZ9kKQUYyvgknFdeKQ.png" /><figcaption>New rating schema</figcaption></figure><p>THANKS FOR READING!!!!</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/200/1*blOI0us--obg4yDOzGFQXQ.gif" /></figure><p>Please like my article if you find it useful and do not forget to follow.</p><p>Contributor: Christain Aliyuda</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=dba67eeaf2c9" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[SQL Data Cleaning Project — FIFA Dataset.]]></title>
            <link>https://medium.com/@victorSom./sql-data-cleaning-project-fifa-dataset-f661b35bfa1e?source=rss-930c9ea6a819------2</link>
            <guid isPermaLink="false">https://medium.com/p/f661b35bfa1e</guid>
            <category><![CDATA[data-cleaning]]></category>
            <category><![CDATA[data-wrangling]]></category>
            <category><![CDATA[data-analysis]]></category>
            <dc:creator><![CDATA[Victor Somadina]]></dc:creator>
            <pubDate>Wed, 15 Mar 2023 17:05:51 GMT</pubDate>
            <atom:updated>2024-05-09T09:53:35.960Z</atom:updated>
            <content:encoded><![CDATA[<h3>SQL Data Cleaning Project — FIFA Dataset.</h3><p><strong>Introduction</strong></p><p>Being one of the organizers of the #DataCleaningChallenge hosted on Twitter, I decided to participate in this project using MySQL as a tool. Let me start by introducing the dataset. The dataset was gotten from Kaggle. You can use the link below to take a look at the dataset.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/812/0*9s3743SQroe-hKjB.jpeg" /></figure><p><a href="https://www.kaggle.com/datasets/yagunnersya/fifa-21-messy-raw-dataset-for-cleaning-exploring">FIFA 21 messy, raw dataset for cleaning/ exploring | Kaggle</a></p><p>In the description, you can see that the dataset was tagged a messy, raw dataset showing that it’s the ideal dataset for a data cleaning project.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/812/0*ZuS8XiItDWo6oWu3.jpeg" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/812/0*vNqSENj4EyP_biSX.jpeg" /><figcaption>A short excel glance on what the dataset is like.</figcaption></figure><p><strong>Identifying Key Issues with the Dataset.</strong></p><p>First, while importing my data into MySQL, I set the encoding to utf-8, this took care of some errors especially in the name of the players and name of some columns. Before we look into general checking of duplicates, null values and all, let’s look at the individual columns to find the issues with them.</p><p>Main table name: <strong>fifa21.</strong> This is my table after I made a copy of the dataset.</p><p><strong>Issue 1 — Height Column:</strong> Using substring (expression, start position int, number of characters to extract int), I separated the numbers from the attached ‘cm’ with the aim of converting to int datatype. In this process, I found out the inconsistency in the Height column, while most values were in ‘cm’ others were measured in feet-inches. I had to make all values consistent by converting feet-inches to cm.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/106/0*ofZByXSsy_ute3A6.jpeg" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/102/0*12QN21xDwfHi07qq.jpeg" /></figure><p>Solution: To make all in cm, I first removed the ‘ and “ in the feet inches measurement to be able to carry out my conversion calculation.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/307/0*SSDxXOvhkbE3i0RV.jpeg" /></figure><p>query to remove ‘ and ‘’ in the values recorded in feet inches measurement.</p><p>Next, I created a temporary table having ID, Feet and inches values in separate columns, after which I converted the feet and inches column to int datatype to enable an easy conversion to centimeter (cm). Next, I added the ‘height in cm’ column to the temporal table where I performed the calculation. I converted the height in cm to varchar () to match the height datatype in the main table and also added cm to make all consistent.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/812/0*C0rL9BiUXCgmDvgz.png" /><figcaption>SQL query to make the height column consistent and measured in cm</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/276/0*Yma-8uvnQpcp2Pn3.jpeg" /><figcaption>Temporal table fifav2 containing the feet inches converted to cm (height_in_cm)</figcaption></figure><p><strong>Issue 2 — weight column:</strong> For this column, Some values have its measurement in lbs while some are in kg.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/115/1*_cJtDAZ3SpikwQCHKm_xVA.jpeg" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/92/1*j6TEq8tW1L1ECDqOt5cHvw.jpeg" /></figure><p>To make it consistent, I converted lbs to kg by multiplying with 0.454 as shown in the code snippet below.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*rr86qNnMhQHlVGKsgvYfFw.png" /><figcaption>query performing the calculation of converting from lbs to kg.</figcaption></figure><p>Next thing is to remove the kg attached, indicate the measurement on the column and convert to an int datatype.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/812/0*iqBKR5vEF1Ktf5Q4.png" /><figcaption>SQL query to remove ‘kg’ and convert to an int datatype</figcaption></figure><p><strong>Issue 3 — Contract column: </strong>For this issue, I want to have a separate column tagged ‘contract start’ which will contain all the years the player’s contract started, and another column tagged ‘contract end’ which will contain the year the contract ended. Some rows shows that few players were on loan but then, it also has the date in which the loan will end in the ‘Loan date end’ column. In the contract end column, instead of having ‘on loan’, I want those rows to contain the date from the ‘loan date end’ column.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/812/0*jtPT4awZ8M38zt7_.png" /><figcaption>SQL queries to achieve the above solution of creating a contract_start and contract_end column</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/166/0*DkZ9jZFgCRKGtkDB.jpeg" /><figcaption>solutions achieved using the queries above.</figcaption></figure><p><strong>Issue 4 — value, wages and release clause column:</strong> These 3 columns have similar problem whereby values are in varchar datatype and will not be efficient for aggregation and relevant calculations. So, for this reason, the ‘M’ and ‘K’ will be converted to the zero representation while the euro sign will be removed and indicated in the column header.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/812/0*6vKQmAHR6T2nAN2X.png" /><figcaption>SQL queries to solve the issue mentioned above</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/191/0*ar8JnZRqn6O4QrR4.jpeg" /><figcaption>solution achieved.</figcaption></figure><p><strong>Issue 5 W_F, SM, IR columns:</strong> This deals with removing the special characters in the columns as shown below.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/228/0*I7u8FjbC39xwdEC7.jpeg" /><figcaption>Issue 5</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/812/0*JWnSbeE2RffKsakx.png" /><figcaption>SQL queries to solve the problem above.</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/235/0*Di8GPg7WYjsXzzFh.jpeg" /><figcaption>snippet to show the problem was solved.</figcaption></figure><p><strong>Issue 6 solving the Nulls in the Hits column:</strong> Note: In the hit column, 1 row have a value of 1.6k which is not consistent with other values in the column. This is to enable us convert to an int datatype.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/812/0*yxol_u9q2NIhSQu2.png" /><figcaption>To solve the 1.6K value</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/83/0*vZtXzR0R8BCXsgMs.jpeg" /><figcaption>1.6k solved</figcaption></figure><p><strong>Issue 7 Club:</strong> Some club names have 1. as an initial which is not proper and should be removed.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/812/0*epPMnMrZK8bQHooN.png" /></figure><p><strong>Issue 8 Dropping irrelevant columns</strong>: The irrelevant columns include.</p><p>Player URL: This is a link column and will not add any valuable insight in the analysis.</p><p>Loan Date End: This column contains a whole lot of null values, meanwhile the values are already referenced in the contract_end column making the loan date end column irrelevant.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/812/0*ZjieUtaJXWaGrvlf.png" /><figcaption>Query to drop irrelevant columns.</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/812/0*br7-PLEG7T6jijA1.jpeg" /><figcaption>New table: fifa21.</figcaption></figure><p>Conclusion: This project helped me hone my data cleaning skills as well my SQL skills.</p><p>Note: There are many ways to kill a rat, there are many ways to solve a problem. After reviewing my queries and you feel there is a simpler method to achieve my solution, please kindly drop in the comment section.</p><p>Also, data analysis is iterative, during the process of the analysis (EDA), another issue might be discovered which will need cleaning the data again.</p><p><strong>Challenges Encountered During Cleaning</strong></p><p>1: In the Hits column, because of the not null constraint initially set, even after modifying the column, it’s not possible in MySQL to replace those null values.</p><p>2: Dropped my table and created another one because of many errors or mistakes.</p><p>Thanks For Reading!!!</p><p>Contributor: Christain Aliyuda.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=f661b35bfa1e" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Solving Danny Ma weekly SQL case study: Week #1 Challenge -Danny’s Dinner]]></title>
            <link>https://medium.com/@victorSom./solving-danny-ma-weekly-sql-case-study-week-1-challenge-dannys-dinner-f4f7bc0aec38?source=rss-930c9ea6a819------2</link>
            <guid isPermaLink="false">https://medium.com/p/f4f7bc0aec38</guid>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[data]]></category>
            <dc:creator><![CDATA[Victor Somadina]]></dc:creator>
            <pubDate>Sat, 18 Feb 2023 10:35:53 GMT</pubDate>
            <atom:updated>2023-02-18T10:35:53.369Z</atom:updated>
            <content:encoded><![CDATA[<p><strong>Solving Danny Ma weekly SQL case study: Week #1 Challenge -Danny’s Dinner</strong></p><p>To test and improve my problem-solving skill, and my ability to use SQL as a tool for data analysis, I delve into the Danny Ma 8 weeks SQL challenge, and to be honest, for someone just starting out in SQL, it can be a bit challenging. I am here to share my solutions for the 10-case study for the week one challenge.</p><p>Danny’s dinner is in need of the help of a data analyst that utilizes SQL as a tool to solve some business problems stated below to help keep the business running.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*IjQ8XODW__ifjJqoBCvNVw.png" /></figure><p>Below are the case studies and my solutions as follows.</p><p>we are provided with 3 tables including.</p><p>1: Menu.</p><p>2: Members.</p><p>3: Sales.</p><p><strong>Case study #1: </strong>What is the total amount each customer spent at the restaurant?</p><p>Solution: Dannys dinner wants to understand the sum of the amount spent by the customers A B C at the restaurant. Sum(price) and group by function could easily solve this problem.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*B7YIBIQXszkLzVNUxCfwUw.png" /><figcaption>SQL code</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/185/1*4U5mXsJvBGLDmt8yvcJIXQ.jpeg" /><figcaption>Output</figcaption></figure><p>As can be seen from the result table above, customer A spent a total of $76 being the highest, followed by customer B and C.</p><p><strong>Case study #2: </strong>How many days has each customer visited the restaurant?</p><p>Solution: Knowing the amount spent by each customer, let us see the days each customer visited the restaurant. Using distinct count takes care of the duplicate times the customer visited because some customers might have visited more than once a day, but we are just looking for the number of days each customer visited.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*N5zDKwneNmrSNU5skTOLEg.png" /><figcaption>SQL code</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/296/1*fI-bPSYC3EzM9Kc5bGz1Hg.jpeg" /><figcaption>output</figcaption></figure><p>Customer B visited for 6 days disregarding the number of times he/she visited for those days, customer A came to the restaurant for 4 days while C is 2 days.</p><p><strong>Case study #3: </strong>What was the first item from the menu purchased by each customer?</p><p>Solution: So, we are looking for the first item purchased by each customer. Using windows functions such as RANK () or DENSE RANK() and OVER (PARTITION BY….) would group and rank the items according to the date at which they were purchased. With common table expression (CTE), we can create a temporal table in which we can sort the first items from each customer with a rank number 1.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/340/1*gkA9HKx-jHZQiew6Vr4EfQ.jpeg" /><figcaption>CTE Output</figcaption></figure><p>Above is the table created by using CTE from which we can sort the first items purchased using WHERE clause.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*cIL3R4cttg3gnd_qkdd6rA.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/266/1*M-pkhoNWyhPUK-xaBkj_RA.jpeg" /></figure><p>For customer A, sushi and curry are the first items purchased while customer B purchased curry first and customer C, Ramen.</p><p><strong>Case study #4: </strong>What is the most purchased item on the menu and how many times was it purchased by all customers?</p><p>Solution: Using DISTINCT and COUNT can do the whole trick. Just that to get that single most purchased product, we order by the count (product) and Limit by 1.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*y8P6xgNI070RWYqviGNYaA.png" /><figcaption>SQL code</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/268/1*t91niiZy3buI-FkNuLwNXg.jpeg" /><figcaption>Output</figcaption></figure><p>Ramen is the most purchased item and was purchased for 8 times by several customers. This is an insightful observation as it shows the product that sells the most.</p><p><strong>Case study #5:</strong> Which item was the most popular for each customer?</p><p>Solution: Just like in the 3rd case, we will create a table from where we would filter out the most popular item for each customer.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*XKpU_ayMGBjOKWWfHjpMDA.png" /><figcaption>SQL code</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/383/1*bIxzMWdeOzcDBqXVS85B9g.jpeg" /><figcaption>CTE output</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/296/1*yBGQiQBF4qJeGAAzf0FGkw.jpeg" /><figcaption>output</figcaption></figure><p>Notice for customer B, Curry, Ramen and Sushi were purchased at the same proportion.</p><p><strong>Case study #6:</strong> Which item was purchased first by the customer after they became a member?</p><p>Solution: To me, this case study was a bit challenging, I had to write a CTE that sorts the products according to the corresponding order dates after the join date (2021–01–07). CTE is shown below.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/346/1*I7_e1qu9JTXeE7gBn5SHnQ.jpeg" /><figcaption>CTE output</figcaption></figure><p>After which I wrote a query that filters the first item purchased using WHERE rank order = 1.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*QxDCB8CeHWc6hQJ8kQthMg.png" /><figcaption>SQL code</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/273/1*-4WynXnRR1oC0rOg151uWg.jpeg" /><figcaption>output</figcaption></figure><p>After the becoming a member, customer A purchased Ramen first while customer B purchased Sushi first. The join date for customer C was not provided in the member&#39;s table.</p><p><strong>Case study #7: </strong>Which item was purchased just before the customer became a member?</p><p>Solution: I see this case study as the opposite of case study #6 where instead of filtering for items purchased after join date, we filter for items purchased before join date. I wrote a CTE to filter items purchased before join date.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/348/1*KF8ln8LsURY9nI1elGzdVw.jpeg" /><figcaption>CTE output</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*N7vFPlW6DTnM9d3nj5ZLLA.png" /><figcaption>SQL code</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/277/1*9-DjBjqQ5carvRly9YhvxA.jpeg" /><figcaption>output</figcaption></figure><p>For customer A, Sushi and Curry were purchased just before the join date being 2021–01–07 while customer B purchased Sushi.</p><p><strong>Case study #8</strong>: What are the total items and amount spent for each member before they became a member?</p><p>Solution: To find total amount spent by customers A and B and total items bought before they became members. This problem is similar to case study #7 as it involves activities before customers became members.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*O0a0m5VkjMS9b3j9P2mtuw.png" /><figcaption>SQL Query</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/265/1*JmhbReiqqE_sr_DNRoHcQQ.jpeg" /><figcaption>query output</figcaption></figure><p><strong>Case study #9</strong>: If each $1 spent equates to 10 points and sushi has a 2x points multiplier — how many points would each customer have?</p><p>Solution: For each $1 spent equates 10 points i.e. price = price * 10 but in a case where the product is sushi, price = price*20. I created another column using this condition then I took the sum.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*CV-yd_EgdygX54cfZPUacw.png" /><figcaption>SQL query</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/194/1*GevTFrXteeRiNpmxfWTe-w.jpeg" /><figcaption>query output</figcaption></figure><p><strong>Case study #10</strong>: 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 customer A and B have at the end of January?</p><p>Solution: Creating a CTE handles the first problem which is adding a column (first week) containing dates to mark one week a customer joins the program starting from the join date. This is achieved using DATE_ADD function. The CTE is shown below.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/451/1*RUJW2JLe4jOlP7XgTXCnhw.jpeg" /><figcaption>CTE output</figcaption></figure><p>After which I wrote a query to filter out total sum of points based on the condition that when order date falls between join date and first week then price = price * 20. If it doesn&#39;t fall between these dates but then the product is sushi, price = price*20. Also, if the order date doesn&#39;t fall between these dates and product is not sushi, then price = price * 10.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*gUYqTvBu0W-jCqz26MwGrg.png" /><figcaption>SQL query</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/182/1*_JcFJ6artfdua6-7XKwdjg.jpeg" /><figcaption>query output</figcaption></figure><p>Thanks for Reading!!!</p><p>Check out solutions to the week 2 challenge!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=f4f7bc0aec38" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Data Analysis Hackathon by 10Alytics: Tackling Health Crisis in Africa]]></title>
            <link>https://medium.com/@victorSom./data-analysis-hackathon-by-10alytics-tackling-health-crisis-in-africa-5de7dec33475?source=rss-930c9ea6a819------2</link>
            <guid isPermaLink="false">https://medium.com/p/5de7dec33475</guid>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[data-visualization]]></category>
            <dc:creator><![CDATA[Victor Somadina]]></dc:creator>
            <pubDate>Thu, 29 Dec 2022 16:36:11 GMT</pubDate>
            <atom:updated>2022-12-29T16:36:11.081Z</atom:updated>
            <content:encoded><![CDATA[<p><strong>Data Analysis Hackathon by 10Alytics: Tackling Health Crisis in Africa</strong></p><p><strong>Tool: Power bi and Excel</strong></p><p>This is a hackathon project sponsored by 10alytics. Though I did not make it to the final stage of the competition which lasted for just 7 hours (12pm-7:30pm), I know I did my best, that&#39;s by the way 😊.</p><p>The project is all about finding the leading causes of health crisis in Africa and proffering a solution to curb the crisis.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/634/1*Y7UFR_enLDxfwfB9ixZaCA.jpeg" /></figure><p><strong>The Datasets</strong></p><p>We were provided with 6 different datasets that required thorough Data assessment and Data cleaning. These datatsets are listed below.</p><p>1: Causes of Deaths.</p><p>2: Number of Deaths.</p><p>3: Medical Doctors per 10000 Population.</p><p>4: Country and Continents Codes.</p><p>5: World Population.</p><p>6: Health Expenditure.</p><p><strong>Data Assessment and Cleaning</strong></p><p>I made use of Excel for the data assessment though I made a little bit of python for clearer assessment of the data. The datasets appeared to have more tidiness issues (including; presence of null values, inaccurate entries, duplicates). The data cleaning process was done in Excel. Check out the snippets below for a view of the cleaned datatsets.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*LfeBGYYT3SHS-hEBKjROpg.jpeg" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*lRCDp2BnePmkx4pCUuHXTQ.jpeg" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*DZB6iyXZ_Fa3cEg-x1kYQw.jpeg" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*GpYefWvz5Kd7hKVIMx6A6A.jpeg" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*sjpbzFJLrJul9w_OYb3nGg.jpeg" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*2l8vCEHWtfgMymLkNrTb3A.jpeg" /></figure><p><strong>Data Modeling in Power bi</strong></p><p>I found relationships between the datasets in which the dimension tables were connected to the fact tables (current health expenditure and medical doctors per 10000 population) forming a star schema model.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/954/1*YCKNaBQcheF-2IQpkz5G5Q.jpeg" /></figure><p><strong>EDA (Exploratory Data Analysis)</strong></p><p>The chart shows a death trend across years from 1990-2019. A hike in death rate was noticed in 2003 recording about 9.6 million deaths in Africa.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/924/1*RSlDXjI_-5AOp10LZ9HXMw.jpeg" /></figure><p>Children under the age of 5 was seen to have the highest death rate.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/438/1*fjT9vz8u7jkdcSvpBlmKuQ.jpeg" /></figure><p>The chart above raised a concern as to what diseases led to the increased death rate in children?. I did another analysis on the diseases that caused the highest death rate in Africa to find out that Cardiovascular diseases, Diarrhea and HIV/AIDS topped the chart, followed by neonatal disorder. All these are children related diseases which led to the increased death rate in children under the age of 5.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/427/1*37svfuexCnPjwptPPieKvQ.jpeg" /></figure><p>Another chart showed the amount of Doctors recorded in each African state. To curb ambiguity, just the top 15 countries were selected.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/481/1*Fbgl5v72q209vzcr6CNDxg.jpeg" /></figure><p>Egypt, Nigeria, Algeria, Tunisia, and South Africa appeared to have the highest number of Doctors, as number Doctors can affect the rate of health crisis in a country. This led to finding the countries with the highest death rate.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/411/1*0X65QVRP15C5p7MrqlRgZA.jpeg" /></figure><p>Nigeria have high amount of Doctors but still record highest death rate. This shows that other factors aside availability of Doctors also affected death rate. This factors might include; unavailablitiy of infrastructures, unavailability of medical equipments and technologies, lack of technical know hows and so on. Also, health expenditures (GDP) can also affect death, this led to analysis on the percentage GDP of African countries.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/476/1*sS4eV_VbXfH-VrbWJ0OVEw.jpeg" /></figure><p><strong>Data Visualization</strong></p><p>I was able to put the charts together to create a simple yet stunning dashboard. Check the image below.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*0-Sql0WS-jWpVITg1LBS0Q.jpeg" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*L9FnhUj93D7e30Tz6VZx-g.jpeg" /></figure><p><strong>Recommendation</strong></p><p>Countries that have high GDP and yet high death rate and low number of Doctors should be looked into, and maybe ensure that higher ratio of the country&#39;s resources be moved to the health sector. Children vaccination to reduce diseases peculiar to kids should be produced and administered to children under the age of 5.</p><p>Medical infrastructures should be made be available as countries such as Nigeria have high number of Doctors and still experiences high death rate.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=5de7dec33475" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>