<?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 Stephanie Anyama on Medium]]></title>
        <description><![CDATA[Stories by Stephanie Anyama on Medium]]></description>
        <link>https://medium.com/@stephanie_a?source=rss-8bc50dcf814a------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*MmJ2SG6b92eHxJ3aOC5bZQ.jpeg</url>
            <title>Stories by Stephanie Anyama on Medium</title>
            <link>https://medium.com/@stephanie_a?source=rss-8bc50dcf814a------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Fri, 20 Nov 2026 08:01:50 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@stephanie_a/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[Data Mart: From Green Gamble to Red Numbers]]></title>
            <link>https://medium.com/@stephanie_a/data-mart-from-green-gamble-to-red-numbers-b34f6894bff4?source=rss-8bc50dcf814a------2</link>
            <guid isPermaLink="false">https://medium.com/p/b34f6894bff4</guid>
            <category><![CDATA[data]]></category>
            <category><![CDATA[business]]></category>
            <category><![CDATA[sustainability]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[sql]]></category>
            <dc:creator><![CDATA[Stephanie Anyama]]></dc:creator>
            <pubDate>Wed, 28 Feb 2024 13:01:49 GMT</pubDate>
            <atom:updated>2025-02-19T13:43:44.389Z</atom:updated>
            <content:encoded><![CDATA[<h4>Case Study 5 of Danny Ma’s #8WeekSQLChallenge</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/678/1*h62dDUyxVTb_r8V9-dXX2w.png" /></figure><h4>INTRODUCTION</h4><p><em>Happy New Year! 🎉</em></p><p>Welcome to 2024, and the <a href="https://8weeksqlchallenge.com/case-study-5/">fifth</a> installment <em>(three more!)</em> in my <strong>#8WeekSQLChallenge</strong> series. It hasn’t been easy, but I vowed to finish this series once I started. So, here we are.</p><p>With the ever-increasing need for sustainable sales and packaging in the wake of climate change, this project looks at the impact of this shift from a fresh and green point of view — The Business Owner. In this project, I used PostgreSQL for data exploration and analysis.</p><p>Ready with your green juice in biodegradable cups and straws? Let’s unpack the insights!</p><h4>THE PROBLEM</h4><p>Data Mart is Danny’s international hybrid supermarket dealing exclusively in farm produce. In June 2020, Danny decided to switch up the entire supply process from farm to table. He introduced sustainable packaging at <em>every </em>step of the supply chain for all products sold at Data Mart. Pretty large scale, yes?</p><p>Well, I got recruited to analyze, quantify, and compare the impact of these large-scale changes on his sales metrics before and after they were introduced. I also made key recommendations that can boost his numbers, depending, of course, on the outcome of my analysis.</p><h4>THE DATA</h4><p>I was provided only with one table for this analysis: weekly_sales. All sales initiated and completed between 2018 and 2020 are chronicled in this table. Thus, the Entity Relationship Diagram (ERD) for this project contains only <strong>one</strong> table.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/395/1*zwaUm-PUKgs0UrnOLfTtJw.png" /><figcaption><strong>The Entity Relationship Diagram (ERD)</strong></figcaption></figure><p>The number of transactions and their cash equivalent, sales, for each week are recorded at the start of every week_date. The data also includes the region of the world where each sale was done, the shopping platform (Retail or Shopify), and each customer’s segment and customer_type separated by age and demographics. We’ll look through all of these metrics as we go.</p><h4>THE ANALYSIS</h4><p>The process was divided into four segments:</p><ol><li>Data Cleansing Steps</li><li>Data Exploration</li><li>Before and After Analysis</li><li>Bonus Question</li></ol><p>The Data Cleansing Steps will be discussed in its dedicated section, which is the next one.</p><h4>DATA CLEANING</h4><p>This section had several requirements, which were:</p><ul><li>Changing week_date to a DATE format</li><li>Adding a week_number as the second column for each week_date value, i.e. in a 52-week year, the position of each week</li><li>Adding a month_number as the third column with the calendar month for each week_date value</li><li>Adding a calendar_year column as the fourth column containing either 2018, 2019 or 2020 values</li><li>Adding a new column called age_band after the original segment column using specific mapping on the <strong>number</strong> inside the segment value</li><li>Adding a new demographic column using specific mapping for the <strong>first</strong> <strong>letter</strong> in the segment values</li><li>Adding a new avg_transaction column as the sales value divided by transactions rounded to 2 decimal places for each record</li></ul><pre>--to change the week_date datatype<br>ALTER TABLE weekly_sales<br>ALTER COLUMN week_date TYPE DATE USING week_date::DATE;<br><br>--creating the clean_weekly_sales table<br>CREATE TABLE clean_weekly_sales AS<br>(SELECT <br> week_date,<br> DATE_PART(&#39;week&#39;, week_date) AS week_number,<br> DATE_PART(&#39;month&#39;, week_date) AS month_number,<br> DATE_PART(&#39;year&#39;, week_date) AS calendar_year,<br> region,<br> platform,<br> segment,<br> (CASE<br>     WHEN RIGHT(segment, 1) = &#39;1&#39; THEN &#39;Young Adults&#39;<br>     WHEN RIGHT(segment, 1) = &#39;2&#39; THEN &#39;Middle Aged&#39;<br>     WHEN RIGHT(segment, 1) IN (&#39;3&#39;, &#39;4&#39;) THEN &#39;Retirees&#39;<br>     ELSE &#39;Unknown&#39;<br>   END) AS age_band, <br> (CASE<br>     WHEN LEFT(segment, 1) = &#39;C&#39; THEN &#39;Couples&#39;<br>     WHEN LEFT(segment, 1) = &#39;F&#39; THEN &#39;Families&#39;<br>     ELSE &#39;Unknown&#39;<br>   END) AS demographic,<br> customer_type, <br> transactions, <br> sales,<br> ROUND((sales::NUMERIC/transactions), 2) AS avg_transactions<br>FROM weekly_sales);<br><br>--to view the table<br>SELECT * FROM clean_weekly_sales;</pre><p>All done! A section of the updated clean_weekly_sales table looks like this:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ttR2avSh46Wq5u7U9w3qXQ.png" /><figcaption><strong>A section of the clean_weekly_sales table</strong></figcaption></figure><h4>EXPLORATORY DATA ANALYSIS</h4><p>In this section, I took a deep dive into Data Mart’s basic metrics. The next three segments of the case study</p><h4>Data Exploration</h4><p>There are 9 questions in this section. Here, I got an overview of the state of affairs at Data Mart.</p><ol><li>What day of the week is used for each week_date value?</li></ol><p><strong><em>Solution</em></strong><em>: Use the TO_CHAR function on the week_date column to find the distinct day of the week used.</em></p><pre>SELECT DISTINCT TO_CHAR(week_date, &#39;Day&#39;) AS first_day<br>FROM clean_weekly_sales;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/101/1*v89CSIINY-pBaQF7secNEQ.png" /><figcaption><strong>The day of the week used is Monday</strong></figcaption></figure><p>2. What range of week numbers are missing from the dataset?</p><p><strong><em>Solution</em></strong><em>: Find out the distinct week numbers and determine the missing ones.</em></p><pre>-- What range of week numbers are missing from the dataset?<br>SELECT DISTINCT week_number<br>FROM clean_weekly_sales<br>ORDER BY 1;<br>  -- Missing weeks: 1-12, 37-52</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/149/1*vJnzu9mFBkJwRqtBfE3cTA.png" /><figcaption><strong>A section through the available week numbers</strong></figcaption></figure><p>3. How many total transactions were there for each year in the dataset?</p><p><strong><em>Solution</em></strong><em>: Find the sum of all the transactions, then group and order by calendar year. I included a little trick, where I added commas into the figures to improve readability.</em></p><pre>--How many total transactions were there for each year in the dataset?<br>SELECT calendar_year, <br>CONCAT(TO_CHAR(SUM(transactions),&#39;9G999G999G999&#39;), &#39; transactions&#39;) total_transactions<br>FROM clean_weekly_sales<br>GROUP BY 1<br>ORDER BY 1;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/361/1*X-fVkOzPaluSJMNF72QnGA.png" /><figcaption><strong>Total transactions increased year-on-year, with 2020 having the most transactions</strong></figcaption></figure><p>4. What is the total sales for each region for each month?</p><p><strong><em>Solution</em></strong><em>: Find the sum of all the sales, then group and order by region and month number.</em></p><pre>-- What is the total sales for each region for each month?<br>SELECT region, month_number, TO_CHAR(week_date, &#39;Month&#39;) month_name,<br>CONCAT(&#39;$&#39;,TO_CHAR(SUM(sales),&#39;9G999G999G999&#39;)) AS total_sales<br>FROM clean_weekly_sales<br>GROUP BY 1, 2, 3<br>ORDER BY 1, 2;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/605/1*X_BuL3v6JV09Anojcx_vPQ.png" /><figcaption><strong>The African Region recorded its highest sales in July ($1.96 billion)</strong></figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/609/1*xbRaVWuN087xCDxz22Cw4Q.png" /><figcaption><strong>The Asian Region recorded its highest sales in April ($1.80 billion)</strong></figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/607/1*g9qKYTbWBdAGksoXvU1TMw.png" /><figcaption><strong>Canada recorded its highest sales in April ($484 million)</strong></figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/603/1*gnIIQ30crHO6423T_Ag2Qw.png" /><figcaption><strong>The European Region recorded its highest sales in April ($127 billion)</strong></figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/605/1*vS65eAjOMqcoiY7-stalgQ.png" /><figcaption><strong>Oceania recorded its highest sales in April ($2.599 billion). This was the most profitable region overall.</strong></figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/607/1*f61DMqmHciXVtfgFNZJ-Pw.png" /><figcaption><strong>The South American Region recorded its highest sales in April ($238 million)</strong></figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/606/1*6pJli1R64-1_55Q9hBxLXg.png" /><figcaption><strong>The USA recorded its highest sales in July ($760 million)</strong></figcaption></figure><p>It is rather interesting to note that, most of the regions recorded their highest sales in April across all three years. The exceptions were Africa and the USA, which saw their sales soar in July. <em>But why?</em></p><p>July is a particularly warm month. So, the spike may be attributed to an increased consumption of fresh produce in that month, increasing outdoor activities such as picnics and barbecues requiring such produce, and seasonality of certain types of produce. We may need to find out <em>what products</em> are sold more in that month.</p><p>5. What is the total count of transactions for each platform?</p><p><strong><em>Solution</em></strong><em>: Find the sum of all the transactions, then group and order by platform (Retail or Shopify).</em></p><pre>--What is the total count of transactions for each platform?<br>SELECT platform, <br>CONCAT(COUNT(transactions), &#39; transactions&#39;) no_of_transactions<br>FROM clean_weekly_sales<br>GROUP BY 1<br>ORDER BY 1;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/349/1*swcDyu6p7CP88TTIWvxO6g.png" /><figcaption><strong>The Retail platform recorded 8,568 transactions, 19 more than Shopify</strong></figcaption></figure><p>6. What is the percentage of sales for Retail vs Shopify for each month?</p><p><strong><em>Solution</em></strong><em>: Calculate the ratio of the sales recorded for each platform to the sales amount for that specific month and year. Multiply that by 100%. As usual, group and order by year and platform.</em></p><pre>--What is the percentage of sales for Retail vs Shopify for each month?<br>SELECT calendar_year, TO_CHAR(week_date, &#39;Month&#39;) month_name, <br>  CONCAT(ROUND(((SUM(CASE WHEN platform = &#39;Retail&#39; THEN sales::NUMERIC END)/SUM(sales))*100.0),2),&#39;%&#39;)<br>   AS Retail_percent,<br>  CONCAT(ROUND(((SUM(CASE WHEN platform = &#39;Shopify&#39; THEN sales::NUMERIC END)/SUM(sales))*100.0),2), &#39;%&#39;)<br>   AS Shopify_percent<br> FROM clean_weekly_sales<br>GROUP BY 1, 2, month_number<br>ORDER BY 1, month_number;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/563/1*nIOfcuPUPUxhhx3Rj_TqMA.png" /><figcaption><strong>Retail accounted for over 96% of total sales across all months and years</strong></figcaption></figure><p>7. What is the percentage of sales by demographic for each year in the dataset?</p><p><strong><em>Solution</em></strong><em>: Use the process described for the previous question, excluding month, of course.</em></p><pre>--What is the percentage of sales by demographic for each year in the dataset?<br>SELECT calendar_year, <br>  CONCAT(ROUND(((SUM(CASE WHEN demographic = &#39;Couples&#39; THEN sales::NUMERIC END)/SUM(sales))*100.0),2), &#39;%&#39;)<br>   AS Couples_percent,<br>  CONCAT(ROUND(((SUM(CASE WHEN demographic = &#39;Families&#39; THEN sales::NUMERIC END)/SUM(sales))*100.0),2), &#39;%&#39;)<br>   AS Families_percent,<br>  CONCAT(ROUND(((SUM(CASE WHEN demographic = &#39;Unknown&#39; THEN sales::NUMERIC END)/SUM(sales))*100.0),2), &#39;%&#39;)<br>   AS Unknown_percent<br> FROM clean_weekly_sales<br>GROUP BY 1<br>ORDER BY 1;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/624/1*5pJ_gY85ayMxghj7ZbRN2A.png" /><figcaption><strong>Apart from the individuals whose demographic is unknown, Families accounted for the most sales in each year.</strong></figcaption></figure><p>I strongly believe that there’s a lot of information lost among the individuals whose age bands and demographics are unknown. An average of about 40% of sales could swing the figures for couples and families either way, and that’s important.</p><p>8. Which age_band and demographic values contribute the most to Retail sales?</p><p><strong><em>Solution</em></strong><em>: Calculate the total sales for each age band and demographic, specifically where the platform is Retail, and group by both factors. Order by total sales obtained.</em></p><pre>--Which age_band and demographic values contribute the most to Retail sales?<br>SELECT age_band, demographic, <br>  CONCAT(&#39;$&#39;,TO_CHAR(SUM(sales),&#39;9G999G999G999G999&#39;)) AS retail_sales<br>FROM clean_weekly_sales<br>WHERE platform = &#39;Retail&#39;<br>GROUP BY 1, 2<br>ORDER BY 3 DESC;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/402/1*r9oFKqUFdIM28SwO0IQT1A.png" /><figcaption><strong>Outside individuals whose age band and demographic are unknown, those in the Retirees age band and Families demographic contributed the most to Retail sales</strong></figcaption></figure><p>Once again, we can observe that a whopping $16 billion has been left unaccounted for, as the age bands and demographics of those customers are unknown. Data Mart will have to improve on their customer database management, as there might <em>(most likely)</em> be untapped insights there.</p><p>9. Can we use the avg_transaction column to find the average transaction size for each year for Retail vs Shopify? If not - how would you calculate it instead?</p><p><strong><em>Solution</em></strong><em>: Calculate the average of the avg_transactions column. In another column, calculate the average normally (total sales divided by total transactions).</em></p><pre>--preferred method of calculating averages<br>SELECT calendar_year, platform, <br>  CONCAT(&#39;$&#39;, ROUND(SUM(sales)/SUM(transactions), 2)) as preferred_average_amt,<br>  CONCAT(&#39;$&#39;, ROUND(AVG(avg_transactions), 2)) as avg_amt<br>FROM clean_weekly_sales<br>GROUP BY 1, 2<br>ORDER BY 1, 3 DESC;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/634/1*JgBzpQQ77LkvH4S03AxGGQ.png" /><figcaption><strong>Total Sales as a ratio of Total Transactions remains the preferable method of calculating average transactions</strong></figcaption></figure><h4>Before and After Analysis</h4><p>Danny wanted to compare sales metrics before and after the introduction of sustainable packaging on June 15th, 2020. These comparisons were made:</p><ul><li>4 weeks before and after</li><li>12 weeks before and after</li><li>with 2018 and 2019 in consideration</li></ul><p>To determine the numbers with the 4-week periods in consideration, I initially found the week number for June 15th, 2020 (25). Thus, to find the total sales and the rise or fall in sales for the period in review, I singled out the figures when the week numbers fall between 21 and 24 (4 weeks before) and 25 and 28 (4 weeks after). This was also applicable to the 12-week periods before and after.</p><p><strong><em>See the results below:</em></strong></p><pre>--What are the total sales for the 4 weeks before and after 2020-06-15? <br>--What is the growth or reduction rate in actual values and percentage of sales?<br>WITH sus_values AS<br>(SELECT<br> SUM(CASE WHEN week_number &gt;=21 AND week_number &lt;=24 THEN sales<br>  END) pre_sustainable,<br> SUM(CASE WHEN week_number &gt;=25 AND week_number &lt;=28 THEN sales<br>  END) post_sustainable<br>FROM clean_weekly_sales<br>WHERE calendar_year = 2020)<br><br>SELECT CONCAT(&#39;$&#39;,TO_CHAR(pre_sustainable,&#39;9G999G999G999G999&#39;)) <br>            AS pre_sustainable, <br>  CONCAT(&#39;$&#39;,TO_CHAR(post_sustainable,&#39;9G999G999G999G999&#39;)) <br>            AS post_sustainable,<br>  CONCAT(&#39;$&#39;,TO_CHAR((post_sustainable - pre_sustainable),&#39;9G999G999G999G999&#39;)) <br>            AS sus_value,<br>  CONCAT(ROUND(((post_sustainable - pre_sustainable)::NUMERIC/pre_sustainable) * 100.0, 2), &#39;%&#39;) <br>            AS rate<br>FROM sus_values;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/553/1*QHNgSJreaWgijK460OlQuQ.png" /><figcaption><strong>In the immediate 4-week period, Data Mart’s sales fell by $26.8 million, corresponding to a 1.15% dip</strong></figcaption></figure><pre>--What about the 12 weeks before and after?<br>WITH total_sus_values AS<br> (SELECT<br>  SUM(CASE WHEN week_number &lt;25 THEN sales<br>   END) pre_sustainable,<br>  SUM(CASE WHEN week_number &gt;=25 THEN sales<br>   END) post_sustainable<br> FROM clean_weekly_sales<br>  WHERE calendar_year = 2020)<br><br>SELECT CONCAT(&#39;$&#39;,TO_CHAR(pre_sustainable,&#39;9G999G999G999G999&#39;)) AS pre_sustainable, <br>  CONCAT(&#39;$&#39;,TO_CHAR(post_sustainable,&#39;9G999G999G999G999&#39;)) AS post_sustainable,<br>  CONCAT(&#39;$&#39;,TO_CHAR((post_sustainable - pre_sustainable),&#39;9G999G999G999G999&#39;)) AS sus_value,<br>  CONCAT(ROUND(((post_sustainable - pre_sustainable)::NUMERIC/pre_sustainable) * 100.0, 2), &#39;%&#39;) <br>   AS total_rate<br>FROM total_sus_values;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/564/1*QNK8PaiRiPvFsxwCRftK4g.png" /><figcaption><strong>In the entire 12–week period in 2020, Data Mart’s sales fell by $152.3 million, corresponding to a 2.14% dip</strong></figcaption></figure><p>We can see that the hopeful green changes in packaging led to unfortunate red numbers for Data Mart. <em>Tough.</em></p><p>When comparing the 12-week periods in 2020 to 2018 and 2019 as seen below, it can be seen that the company made losses only in 2020, the year the changes in packaging were made. It can be estimated that the time of year was not the direct cause of this fall.</p><pre>WITH sus_values AS<br>(SELECT<br>  calendar_year,<br> SUM(CASE WHEN week_number &gt;=21 AND week_number &lt;=24 THEN sales<br>  END) pre_sustainable,<br> SUM(CASE WHEN week_number &gt;=25 AND week_number &lt;=28 THEN sales<br>  END) post_sustainable<br>FROM clean_weekly_sales<br>GROUP BY 1<br>ORDER BY 1)<br><br>SELECT  calendar_year,<br>  CONCAT(&#39;$&#39;,TO_CHAR(pre_sustainable,&#39;9G999G999G999G999&#39;)) AS pre_sustainable, <br>  CONCAT(&#39;$&#39;,TO_CHAR(post_sustainable,&#39;9G999G999G999G999&#39;)) AS post_sustainable,<br>  CONCAT(&#39;$&#39;,TO_CHAR((post_sustainable - pre_sustainable),&#39;9G999G999G999G999&#39;)) AS difference,<br>  CONCAT(ROUND(((post_sustainable - pre_sustainable)::NUMERIC/pre_sustainable) * 100.0, 2), &#39;%&#39;) <br>   AS rate<br>FROM sus_values;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/704/1*c7ke-hB35IeZGO9DhamIaw.png" /><figcaption><strong>2020 was the only year that recorded losses of well over $26 million</strong></figcaption></figure><h4>Bonus Question</h4><p>In this final question, I was tasked to find out what region, platform, demographic, age band, and customer type had the highest negative impacts on sales in the 12-week periods before and after the introduction of sustainable packaging.</p><p><strong><em>Solution</em></strong><em>: Calculate the amount of sales pre- and post-introduction. Group by region, platform, demographic, age band, or customer type as required. The query below can be adjusted for any of the parameters listed.</em></p><pre>WITH total_sus_values AS<br> (SELECT region,<br>  SUM(CASE WHEN week_number &lt;25 THEN sales<br>   END) pre_sustainable,<br>  SUM(CASE WHEN week_number &gt;=25 THEN sales<br>   END) post_sustainable<br> FROM clean_weekly_sales<br>  WHERE calendar_year = 2020<br> GROUP BY 1)<br><br>SELECT region, --adjust as needed<br>  CONCAT(&#39;$&#39;,TO_CHAR(pre_sustainable,&#39;9G999G999G999G999&#39;)) AS pre_sustainable, <br>  CONCAT(&#39;$&#39;,TO_CHAR(post_sustainable,&#39;9G999G999G999G999&#39;)) AS post_sustainable,<br>  CONCAT(&#39;$&#39;,TO_CHAR((post_sustainable - pre_sustainable),&#39;9G999G999G999G999&#39;)) AS difference,<br>  CONCAT(ROUND(((post_sustainable - pre_sustainable)::NUMERIC/pre_sustainable) * 100.0, 2), &#39;%&#39;) <br>   AS rate<br>FROM total_sus_values <br>GROUP BY 1, pre_sustainable, post_sustainable<br>ORDER BY 5 DESC;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/740/1*3WDWsDZ_LZPR_KUZODLsLQ.png" /><figcaption><strong>Asia saw the highest negative impact on sales, with a 3.26% drop. Europeans were quite accepting of the change</strong></figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/739/1*PZ8w3xCGiREsTAA_Vhiaow.png" /><figcaption><strong>Although the Retail had more sales overall, there was a 2.43% dip associated with the changes</strong></figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/683/1*hl68RjDGzwCiywCcGon5qQ.png" /><figcaption><strong>For customers whose demographic was known, those in the Families demographic were rather uninterested, giving a 1.82% dip in sales</strong></figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/664/1*HJuBD-YKx2RsMTVQcLhY-Q.png" /><figcaption><strong>For customers whose age band was known, those in the Middle Aged band gave a 1.97% dip in sales</strong></figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/735/1*lDDNycc7eJnwDaNjo8FfDg.png" /><figcaption><strong>Guest customers had the highest negative impact, causing a whopping 3% drop</strong></figcaption></figure><h4>RECOMMENDATIONS</h4><p>Data Mart has been hit <em>hard. </em>But it’s not an irredeemable situation. Moving forward, here are the recommendations I have decided to give Danny and his team:</p><ol><li>The customer registration process has to be improved upon. Customers with unknown demographics and age bands should be registered properly. This will clearly define these groups’ contributions to sales, and strategies that will improve such contributions.</li><li>Customers should be encouraged to return all of Data Mart’s old non-sustainable packaging in their possession. A point reward system can be put in place as an incentive. These points can be used for discounts on certain products and redeemed on any platform, whether Retail or Shopify. Europeans will be rather excited by this, as <a href="https://www.bloomberg.com/netzeropathfinders/best-practices/deposit-return-scheme-germany/#:~:text=Consumers%20in%20Germany%20are%20rewarded,%E2%82%AC0.08%20per%20glass%20bottle.">Germans</a> have a similar system in place.</li><li>Extra charges can be placed on single-use or non-sustainable packaging to sway customers away from them and towards sustainable packaging.</li><li>Customer experience forms should be distributed on either sale platform, especially among the groups most negatively impacting sales, to determine their sentiments on the new packaging and possible suggestions for improvement.</li><li>Sequel to point 4 above, surveys should be carried out before the introduction of future changes at Data Mart, large-scale or not. This is so that a proper projection can be made, and revenue will not have to drop as much.</li><li>A guest-to-new customer pipeline should be considered, as it was seen that new customers contributed positively to sales. New customers should be granted discounts on their first purchase to foster retention.</li></ol><h4>FINAL WORDS</h4><p>This was not too tough to solve, as the data was domiciled in one place. Thankfully, it provided a fresh perspective into the Green Movement, and how it doesn’t have to spell ruin for both the environment and a business owner’s pockets. Looking forward to Case Study 6!</p><p>As always, I am open to questions and corrections on this project and collaborations on future projects.</p><p>Till my next article, <em>¡adiós!</em></p><p>PS: Follow me on <a href="https://twitter.com/Naeto__">Twitter</a> and connect with me on <a href="https://linkedin.com/in/stephanieanyama">LinkedIn</a>. View my portfolio <a href="https://stephanieanyama.carrd.co/">here</a>. All code used and the link to the interactive report are stored in the Data Mart <a href="https://github.com/Stephanie-Anyama/8-Week-SQL-Challenge/tree/main/Data%20Mart">folder</a> on my GitHub.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=b34f6894bff4" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Data Bank — Where SQL Queries are My Deposits]]></title>
            <link>https://medium.com/@stephanie_a/data-bank-where-sql-queries-are-my-deposits-1d3855519e6a?source=rss-8bc50dcf814a------2</link>
            <guid isPermaLink="false">https://medium.com/p/1d3855519e6a</guid>
            <category><![CDATA[postgres]]></category>
            <category><![CDATA[data-analytics]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[powerpoint]]></category>
            <category><![CDATA[sql]]></category>
            <dc:creator><![CDATA[Stephanie Anyama]]></dc:creator>
            <pubDate>Mon, 23 Oct 2023 15:01:05 GMT</pubDate>
            <atom:updated>2025-02-19T13:44:47.306Z</atom:updated>
            <content:encoded><![CDATA[<h3>Data Bank — Where SQL Queries were My Deposits</h3><h4>Case Study 4 of Danny Ma’s #8WeekSQLChallenge</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/579/1*zSfja1peKvOpnwwieXrgwQ.png" /></figure><h4>INTRODUCTION</h4><p>Hi! If you’ve been following this series of articles, you’ll know I took quite the hiatus. <em>Life. </em>We’ll just get right back into it.</p><p>This is the <a href="https://8weeksqlchallenge.com/case-study-4/">fourth</a> installment in my <strong>#8WeekSQLChallenge</strong> series by Danny Ma. This project is important, as it was used to calculate growth metrics, customers’ activities over specified periods of time and the amount of cloud data customers will be entitled to, based on their activity. For this project, I opted to use PostgreSQL for the first time, instead of MySQL which I used for the first three.</p><p>Interested? Let’s begin.</p><h4>THE PROBLEM</h4><p>Data Bank is a Neo-bank with no physical branches (think Kuda, if you’re Nigerian). As such, it isn’t only for banking activities, they also have the world’s most secure distributed data storage platform!</p><p>Customers are allocated cloud data storage limits which are directly linked to how much money they have in their accounts. The management team at Data Bank want to increase their total customer base — but also need some help tracking just how much data storage their customers will need.</p><h4>THE DATA</h4><p>Danny provided three tables: regions, customer_nodes and customer_transactions, connected as shown in the following entity relationship diagram (ERD):</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/830/1*0B3FpYMGuCEdHV9YGbHtgg.png" /><figcaption><strong>The Entity Relationship Diagram (ERD)</strong></figcaption></figure><ul><li>regions: This table represents the continents of the world with their unique identifiers. Note that Antartica was not listed (obviously), and North and South America were merged into one America.</li><li>customer_nodes: Customers are randomly distributed across the nodes according to their region — this also specifies exactly which node contains both their cash and data. This random distribution changes frequently to reduce the risk of hackers getting into Data Bank’s system and stealing customer’s money and data.</li><li>customer_transactions: This table stores all customer transactions, which could be deposits, withdrawals and purchases, made using their Data Bank debit card.</li></ul><h4>DATA EXPLORATION</h4><p>This study was split into 5 sections, but the fifth will be discussed in the next part of this article.</p><ol><li>Customer Nodes Exploration</li><li>Customer Transactions</li><li>Data Allocation Challenge</li><li>Extra Challenge</li><li>Extension Request</li></ol><p><em>To save time to discuss other longer parts of this project, I’ll only write my answers to the first 4 questions in the Customer Nodes Exploration and Customer Transactions sections.</em></p><h4><strong>A. Customer Nodes Exploration</strong></h4><ol><li>How many unique nodes are there on the Data Bank system?</li></ol><pre>SELECT COUNT(DISTINCT node_id)<br>FROM customer_nodes;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/152/1*aFrMHSS7EYHTCzKC5xbt0g.png" /><figcaption><strong>There are 5 unique nodes on the Data Bank system</strong></figcaption></figure><p>2. What is the number of nodes per region?</p><pre>SELECT r.region_name, COUNT(DISTINCT n.node_id)<br>FROM regions as r<br>JOIN customer_nodes as n<br>ON r.region_id = n.region_id<br>GROUP BY 1<br>ORDER BY 2;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/335/1*LyRSG93ooH4zpd-WTIahPQ.png" /><figcaption><strong>Each region has 5 nodes</strong></figcaption></figure><p>3. How many customers are allocated to each region?</p><pre>SELECT r.region_name, COUNT(DISTINCT n.customer_id)<br>FROM regions as r<br>JOIN customer_nodes as n<br>ON r.region_id = n.region_id<br>GROUP BY 1<br>ORDER BY 2 DESC;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/337/1*ffRLEoXorhAcNSikGBGw0g.png" /><figcaption><strong>Australia and Europe were allocated the highest and lowest number of customers respectively</strong></figcaption></figure><p>4. How many days on average are customers reallocated to a different node?</p><pre>SELECT CONCAT(CEIL(AVG(day_to_change)), &#39; days&#39;) as avg_day_to_change<br>FROM<br>(SELECT customer_id, start_date, node_id,<br>LEAD(start_date) OVER (PARTITION BY customer_id ORDER BY start_date) <br>  AS next_date,<br>CASE WHEN LEAD(start_date) OVER (PARTITION BY customer_id ORDER BY start_date) IS NOT NULL <br>THEN (LEAD(start_date) OVER (PARTITION BY customer_id ORDER BY start_date) - start_date) <br>  ELSE 0 END AS day_to_change<br>FROM customer_nodes) T1;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/227/1*HnczjNwhVqncin5JHSrmpg.png" /><figcaption>Customers are reallocated every fortnight on average</figcaption></figure><h4><strong>B. Customer Transactions</strong></h4><ol><li>What is the unique count and total amount for each transaction type?</li></ol><pre>SELECT txn_type, COUNT(DISTINCT customer_id) AS unique_count,<br>SUM(txn_amount) AS total<br>FROM customer_transactions<br>GROUP BY 1<br>ORDER BY 2 DESC;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/475/1*biaEnxe0s1jlvM2qFHzPMA.png" /><figcaption>Deposits were the most transactions made, and made the highest amount</figcaption></figure><p>2. What is the average total historical deposit counts and amounts for all customers?</p><pre>SELECT ROUND(AVG(deposit_count)) AS avg_deposit_count, <br>ROUND(AVG(deposit_amount)) AS avg_deposit_amount<br>FROM<br>(SELECT customer_id, COUNT(txn_type) AS deposit_count, SUM(txn_amount) AS deposit_amount<br>FROM customer_transactions<br>WHERE txn_type = &#39;deposit&#39;<br>GROUP BY customer_id) deposit_txns;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/352/1*ixtJURaW0aT3Ai9k2jGDsQ.png" /><figcaption><strong>Customers made 5 deposits on average, with $2718 as the average amount</strong></figcaption></figure><p>3. For each month — how many Data Bank customers make more than 1 deposit and either 1 purchase or 1 withdrawal in a single month?</p><pre>WITH type_counts AS<br>(SELECT customer_id, <br>COUNT(CASE WHEN txn_type = &#39;deposit&#39; THEN customer_id END) AS deposit_count,<br>COUNT(CASE WHEN txn_type = &#39;purchase&#39; THEN customer_id END) AS purchase_count,<br>COUNT(CASE WHEN txn_type = &#39;withdrawal&#39; THEN customer_id END) AS withdrawal_count<br>FROM customer_transactions<br>GROUP BY customer_id)<br>SELECT TO_CHAR(c.txn_date, &#39;Month&#39;) as month_name,<br>CONCAT(COUNT(DISTINCT t.customer_id), &#39; customers&#39;) AS ret_customers<br>FROM type_counts t<br>JOIN customer_transactions c<br>ON t.customer_id = c.customer_id<br>WHERE t.deposit_count &gt; 1 AND (t.purchase_count &gt; 0 OR t.withdrawal_count &gt; 0)<br>GROUP BY 1<br>ORDER BY 2 DESC;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/323/1*Cjr_tU--6-DrBrXSc7Ew5A.png" /><figcaption><strong>January had the highest amount of returning customers, by this metric</strong></figcaption></figure><p>4. What is the closing balance for each customer at the end of the month?</p><pre>WITH customer_funds AS<br>(SELECT customer_id, TO_CHAR(txn_date, &#39;Month&#39;) as month_name,<br>SUM(CASE WHEN txn_type = &#39;deposit&#39; THEN txn_amount ELSE -1 * txn_amount END) AS total_amount<br>FROM customer_transactions<br>GROUP BY customer_id, 2<br>ORDER BY 1)<br>SELECT customer_id, month_name,<br>SUM(total_amount) OVER(PARTITION BY customer_id ORDER BY month_name) AS closing_balance<br>FROM customer_funds;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/461/1*_MmKqDkV9W04dgskbVkDiQ.png" /><figcaption>This is a section of each customer’s monthly closing balance</figcaption></figure><h4><strong>C. Data Allocation Challenge</strong></h4><p>The Data Bank team wanted to run an experiment where different groups of customers would be allocated data using 3 different options:</p><ul><li>Option 1: data is allocated based off the amount of money at the end of the previous month using a running customer balance column that includes the impact of each transaction.</li><li>Option 2: data is allocated on the average amount of money kept in the account in the previous 30 days using customer balance at the end of each month.</li><li>Option 3: data is updated real-time using minimum, average and maximum values of the running balance for each customer.</li></ul><p>This is to help the Data Bank team estimate how much data will need to be provisioned for each option.</p><p><em>The queries are really long! So to make it easier, I’ll only add the queries to generate the first elements. The queries to determine the allocations will be on my GitHub.</em></p><pre>-- running customer balance column that includes the impact of each transaction<br>WITH amounts AS<br>(SELECT customer_id, EXTRACT(&#39;Month&#39; FROM txn_date) as month_num, txn_type, txn_amount, <br>CASE WHEN txn_type = &#39;deposit&#39; THEN txn_amount ELSE -1 * txn_amount END AS total_amount<br>FROM customer_transactions<br>ORDER BY customer_id)<br><br>SELECT customer_id, month_num, total_amount, <br>SUM(total_amount) OVER (PARTITION BY customer_id, month_num <br> ORDER BY month_num ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)<br>   AS running_balance<br>FROM amounts;<br><br>-- customer balance at the end of each month<br>SELECT customer_id, EXTRACT(&#39;Month&#39; FROM txn_date) as month_num, <br>SUM(CASE WHEN txn_type = &#39;deposit&#39; THEN txn_amount ELSE -1 * txn_amount END) AS monthly_balance<br>FROM customer_transactions<br>GROUP BY 1, 2<br>ORDER BY customer_id<br>;<br><br>-- minimum, average and maximum values of the running balance for each customer<br>WITH amounts AS<br>(SELECT customer_id, EXTRACT(&#39;Month&#39; FROM txn_date) as month_num, txn_type, txn_amount, <br>CASE WHEN txn_type = &#39;deposit&#39; THEN txn_amount ELSE -1 * txn_amount END AS total_amount<br>FROM customer_transactions<br>ORDER BY customer_id),<br><br>balances AS <br>(SELECT customer_id, month_num, total_amount, <br>SUM(total_amount) OVER (PARTITION BY customer_id, month_num <br> ORDER BY month_num ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)<br>   AS running_balance<br>FROM amounts)<br><br>SELECT customer_id, <br>MIN(running_balance) as min_bal, <br>ROUND(AVG(running_balance),2) AS avg_bal, <br>MAX(running_balance) AS max_bal<br>FROM balances<br>GROUP BY 1;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/599/1*b7D3wk5XJO5asRzj-e3lbw.png" /><figcaption><strong>From top to bottom, each table shown in the picture above determines the allocation based on Options 1, 2 and 3</strong></figcaption></figure><p>This was a very tricky section but in summary: Data Bank wanted to use the elements listed above to determine the most efficient way to allocate cloud storage space to their customers. Would that be running balance, average monthly balance or min-avg-max running balance? For me, it was Option 1 (running balance, <em>table 1</em>). I felt so for two reasons:</p><ol><li>It was more equitable, as the figures weren’t far off from one another.</li><li>Using an average to determine the allocation could be heavily skewed by a huge purchase (which would add disproportionately) or a huge withdrawal (which would do the opposite).</li></ol><h4><strong>D. Extra Challenge</strong></h4><p>Data Bank wanted to determine allocations based on a 6% per annum interest rate. I had to whip out some secondary school mathematics for this one. Recall the formula for simple interest? It was very useful here.</p><pre>WITH monthly_balances AS<br>(SELECT customer_id, EXTRACT(&#39;Month&#39; FROM txn_date) as month_num, <br> <br>SUM(CASE WHEN txn_type = &#39;deposit&#39; THEN txn_amount ELSE -1 * txn_amount END) AS monthly_balance<br>FROM customer_transactions<br>GROUP BY 1, 2<br>ORDER BY customer_id),<br><br>interest_earned AS<br>(SELECT *,<br>ROUND(((monthly_balance * 6 * 1)/(100.0 * 12)),2) AS interest<br>FROM monthly_balances<br>GROUP BY customer_id, month_num, monthly_balance<br>ORDER BY customer_id, month_num),<br><br>total_earnings AS<br>(SELECT customer_id, month_num, <br>(monthly_balance + interest) AS earnings<br>FROM interest_earned<br>GROUP BY 1, 2, 3<br>ORDER BY 1, 2)<br><br>SELECT month_num,<br>SUM(CASE WHEN earnings &lt; 0 THEN 0 ELSE earnings END) as allocation<br>FROM total_earnings<br>GROUP BY 1<br>ORDER BY 1;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/361/1*oTyBxI_Pay5J0aoZsrb6Jg.png" /><figcaption><strong>Allocations using the 6% interest rate</strong></figcaption></figure><p>Now <em>this</em> is an improvement on the running balance option from the last section, as it still made use of the balances (but as interest amounts), and dealt with smaller amounts, thus requiring less space. Easily the definition of efficiency.</p><h4>DATA VISUALIZATION &amp; REPORTING</h4><p>This is the <strong>Extension Request</strong> section of the project. In this section, I was asked to create a PowerPoint presentation containing the key insights derived from the data and all the questions asked.</p><blockquote>Check out the PowerPoint presentation <a href="https://1drv.ms/p/s!AvMjrweIRpG4v1Yetb8z7sftDqmw?e=tccsIi">here</a>.</blockquote><p>Based on the questions answered, the following <strong>recommendations</strong> were made:</p><ol><li>To improve the security of the reallocations, the reallocation rate should reduce to an average of <strong>7 days</strong>. If these reallocations can’t take place faster, they should be optimized to quicken during peak periods, such as around the end of year.</li><li>Customers should be duly notified of their reallocations at most 2 days before they occur.</li><li>Cloud Data storage spaces should be shared based on accrued interest for efficiency and equitability.</li><li>Saving plans should be included into customers’ payments to increase their earned interest and storage allocations, and ultimately, to earn money for Data Bank.</li></ol><h4>FINAL WORDS</h4><p>This was a very difficult challenge, particularly in the Data Allocation sections. I used a lot more mathematics here, in calculating running balances, medians and percentiles, and simple interest using specified rates in SQL. It also highlighted the importance of <em>good</em> presentation skills. Looking forward to Case Study 5!</p><p>As always, I am open to questions and corrections on this project and collaborations on future projects.</p><p>Till my next article, <em>¡adiós!</em></p><p>PS: Follow me on <a href="https://twitter.com/Naeto__">Twitter</a> and connect with me on <a href="https://linkedin.com/in/stephanieanyama">LinkedIn</a>. View my portfolio <a href="https://stephanieanyama.carrd.co/">here</a>. All code used and the link to the interactive report are stored in the Data Bank <a href="https://github.com/Stephanie-Anyama/8-Week-SQL-Challenge/tree/main/Data%20Bank">folder </a>on my GitHub.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=1d3855519e6a" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[SQL-Powered Dining: Foodie-Fi’s Recipe for Subscription Success]]></title>
            <link>https://medium.com/@stephanie_a/sql-powered-dining-foodie-fis-recipe-for-subscription-success-582bf2ebf230?source=rss-8bc50dcf814a------2</link>
            <guid isPermaLink="false">https://medium.com/p/582bf2ebf230</guid>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[business]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[data-analytics]]></category>
            <dc:creator><![CDATA[Stephanie Anyama]]></dc:creator>
            <pubDate>Mon, 11 Sep 2023 12:01:37 GMT</pubDate>
            <atom:updated>2025-02-19T13:46:34.012Z</atom:updated>
            <content:encoded><![CDATA[<h4>Case Study 3 of Danny Ma’s #8WeekSQLChallenge</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/651/1*n_qR5UYVSpCCMSYhmzKs0Q.png" /></figure><h4>INTRODUCTION</h4><p>This is the <a href="https://8weeksqlchallenge.com/case-study-3/">third</a> installment of my <strong>#8WeekSQLChallenge</strong> series by Danny Ma. This case study used subscription style digital data to answer important business questions. As such, this case study wasn’t as heavy on the SQL scripting as previous ones were, but focused on the <em>real</em> need for SQL — solving real business problems.</p><h4>THE PROBLEM</h4><p>Danny launched his new startup Foodie-Fi in 2020 to sell monthly and annual subscriptions, giving customers unlimited on-demand access to exclusive cooking videos from around the world. This was like Netflix, but for food only. The startup was created with a data driven mindset and wanted to ensure all future investment decisions and new features were decided using data.</p><h4>THE DATA</h4><p>As I stated earlier, this project was less focused on SQL functions and buzzwords, and more on business decisions. The data provided was made up of only 2 tables, existing within the foodie-fi database schema:</p><ul><li>plans: This table contains each unique plan available to customers, with their prices — Trial, Basic monthly, Pro monthly and Pro annual. When customers cancel their Foodie-Fi service, their plan is stated to be Churn.</li><li>subscriptions: This table shows each uniquely identified customer and their plans purchased across the period. The start_date column represents one of three things: the date of first registration with Foodie-Fi, date of change from one plan to another or the date a customer churns.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/638/1*5F5QapmDWvSHHTe-etTPeA.png" /><figcaption>The Entity Relationship Diagram (ERD)</figcaption></figure><h4>DATA EXPLORATION</h4><p>This case study was split into 4 sections:</p><ol><li>Customer Journey</li><li>Data Analysis Questions</li><li>Challenge Payment Question</li><li>Outside-the-box Questions</li></ol><h4><strong>Customer Journey</strong></h4><p>This section asked me to describe each unique onboarding journey for 8 selected customers from the sample table provided. To do this, I wrote a short query to extract the required customers. I’ll only describe the journeys of the first 4 customers.</p><pre>SELECT s.customer_id, s.plan_id, p.plan_name, s.start_date <br>FROM subscriptions s<br>JOIN plans p<br>ON s.plan_id = p.plan_id<br>WHERE s.customer_id IN (1,2,11,13,15,16,18,19);</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/353/1*MN9gHfCGF_EtINfR4jDD2Q.png" /><figcaption><strong>A section through the result set</strong></figcaption></figure><ol><li>Customer 1 began the trial plan on August 1st 2020, after which they moved to the basic monthly plan a week later, when the trial plan ended. This customer hasn’t churned.</li><li>Customer 2 began the trial plan on September 20th 2020, after which they moved to the pro annual plan a week later, when the trial plan ended. This customer hasn’t churned.</li><li>Customer 11 began the trial plan on November 19th 2020, after which they churned a week later, when the trial plan ended.</li><li>Customer 13 began the trial plan on 15th December 2020, after which they moved to the basic monthly plan a week later, when the trial plan ended. On 29th March 2021, they upgraded from the basic monthly to the pro monthly plan, and hasn’t churned.</li></ol><h4>Data Analysis Questions</h4><ol><li>How many customers has Foodie-Fi ever had?</li></ol><p><strong>Solution:</strong> Count the number of distinct customer ids.</p><pre>SELECT COUNT(DISTINCT customer_id) <br>FROM subscriptions;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/131/1*ojLA_U94SnLEtYB8U3yL2A.png" /><figcaption><strong>Foodie Fi attracted 1000 unique customers within the period</strong></figcaption></figure><p>2. What is the monthly distribution of trial plan start_date values for our dataset, using the start of the month as the group by value?</p><p><strong>Solution: </strong>Select the month name and number, count the number of plans per month, then group and order the result by the month number to get the monthly distribution. Be careful to filter for entries where the plan_id was 0 (trial).</p><pre>SELECT MONTH(start_date) as month_of_sub,<br>MONTHNAME(start_date) as name_of_month,<br>COUNT(plan_id) no_of_plans<br>FROM subscriptions<br>WHERE plan_id = 0<br>GROUP BY MONTH(start_date)<br>ORDER BY MONTH(start_date);</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/325/1*DUk15T_wAbD8tykCJYrfBg.png" /><figcaption><strong>March had the highest number of subscriptions, while February had the least</strong></figcaption></figure><p>3. What plan start_date values occur after the year 2020 for our dataset? Show the breakdown by count of events for each plan_name</p><p><strong>Solution: </strong>Select all the years after 2020 and the names of all available plans. Join the plans and subscriptions table using plan_id as the common column. Group by year and plan name, and order by year.</p><pre>SELECT YEAR(s.start_date) year_of_plan, p.plan_name, <br>      COUNT(s.plan_id) no_of_plans<br>FROM subscriptions as s<br>JOIN plans as p<br>ON s.plan_id = p.plan_id<br>WHERE YEAR(s.start_date) &gt; 2020<br>GROUP BY YEAR(s.start_date), p.plan_name<br>ORDER BY YEAR(s.start_date);</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/299/1*skS7NY5ouD9glWd_xeEM8g.png" /><figcaption><strong>Customers mainly churned in 2021, at a churn rate of 35.15%</strong></figcaption></figure><p>4. What is the customer count and percentage of customers who have churned rounded to 1 decimal place?</p><p><strong>Solution:</strong> While filtering for entries with a plan_id of 4 (churn), count the number of distinct customer IDs and calculate the percentage of customers in this group. Round the percentage up to 1 decimal place, and concatenate the % sign into the result.</p><pre>SELECT COUNT(DISTINCT customer_id) as no_of_customers, <br>CONCAT(ROUND((COUNT(DISTINCT customer_id)/(SELECT COUNT(DISTINCT customer_id) FROM subscriptions)) * 100.0, 1), &#39;%&#39;) as percent_churn<br>FROM subscriptions<br>WHERE plan_id = 4;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/231/1*d_QETGcwTypOBarvzxfAXA.png" /><figcaption><strong>307 customers churned, at a rate of 30.7%</strong></figcaption></figure><p>5. How many customers have churned straight after their initial free trial — what percentage is this rounded to the nearest whole number?</p><p><strong>Solution:</strong> In this case, calculate the difference in plan_id and the intervals for each customer for each minimum and maximum start date. Afterwards, filter the result for entries for those with a plan_diff of 4 and interval of 7. Count the number of distinct customer IDs and calculate the percentage of customers in this group. Round the percentage up, and concatenate the % sign into the result.</p><pre>SELECT COUNT(DISTINCT customer_id) as no_of_customers, <br>CONCAT(CEIL((COUNT(DISTINCT customer_id)/(SELECT COUNT(DISTINCT customer_id) FROM subscriptions)) * 100.0), &#39;%&#39;) as straight_churn<br>FROM<br>(SELECT customer_id, MAX(plan_id) - MIN(plan_id) as plan_diff, <br>MAX(start_date) - MIN(start_date) as interval_diff<br>FROM subscriptions<br>GROUP BY 1<br>HAVING MAX(plan_id) - MIN(plan_id) = 4 AND MAX(start_date) - MIN(start_date) = 7)T1;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/231/1*syzat62nmaaVB1LQjaVcsQ.png" /><figcaption><strong>72 customers churned directly from their trial plans, at a rate of 8%</strong></figcaption></figure><p>6. What is the number and percentage of customer plans after their initial free trial?</p><p><strong>Solution: </strong>While filtering for entries with a plan_id that is <em>not</em> 0 (trial), count the number of distinct customer IDs and calculate the percentage of customers in this group. Round the percentage up, and concatenate the % sign into the result.</p><pre>SELECT p.plan_name, COUNT(customer_id) as customer_count,<br>CONCAT(ROUND(((COUNT(customer_id)/1000) * 100.0), 1), &#39;%&#39;) as percent_breakdown<br>FROM<br>(SELECT customer_id, MAX(plan_id) as top_id, MAX(start_date) as top_date<br>FROM subscriptions as s<br>GROUP BY 1)T1<br>JOIN plans as p<br>ON top_id = p.plan_id<br>WHERE p.plan_name != &#39;trial&#39;<br>GROUP BY 1<br>ORDER BY 2 DESC;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/358/1*hHaMSTNpT3614aI34VdfSA.png" /><figcaption><strong>The most used plan is the Pro Monthly plan</strong></figcaption></figure><p>7. What is the customer count and percentage breakdown of all 5 plan_name values at 2020-12-31?</p><p><strong>Solution: </strong>Extract the entries with the highest start_date per customer to find their last running plan, and filter by year = 2020. Count the number of distinct customer IDs and calculate the percentage of customers for each plan using a subquery. Round the percentage up, and concatenate the % sign into the result.</p><pre>SELECT p.plan_name, COUNT(customer_id) as customer_count,<br>CONCAT(ROUND(((COUNT(customer_id)/1000) * 100.0), 1), &#39;%&#39;) as percent_breakdown<br>FROM<br>(SELECT customer_id, MAX(plan_id) as top_id, MAX(start_date) as top_date<br>FROM subscriptions as s<br>WHERE YEAR(start_date) = 2020<br>GROUP BY 1)T1<br>JOIN plans as p<br>ON top_id = p.plan_id<br>GROUP BY 1;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/376/1*vj6VytcCDC8tAWoukhxl3A.png" /><figcaption><strong>By the end of 2020, the most used plan was the Pro monthly plan (32.6%)</strong></figcaption></figure><p>8. How many customers have upgraded to an annual plan in 2020?</p><p><strong>Solution:</strong> While filtering for entries with a plan_id of 3 (pro annual) and year 2020, count the number of distinct customer IDs.</p><pre>SELECT COUNT(DISTINCT customer_id) as customer_count<br>FROM subscriptions<br>WHERE YEAR(start_date) = 2020 AND plan_id = 3;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/269/1*kSrrldW9rzqND_ogdwQKMg.png" /><figcaption><strong>195 customers upgraded to an annual plan in 2020</strong></figcaption></figure><p>9. How many days on average does it take for a customer to subscribe to an annual plan from the day they join Foodie-Fi?</p><p><strong>Solution: </strong>Using the previous solution as a common table expression including the start_date as the date of upgrade, find the date difference between the date of first registration and the upgrade date. Join the CTE to the subscriptions table on the customer_id column. Find the average difference and concatenate ‘ days’ into the result.</p><pre>WITH T1 AS (SELECT customer_id, start_date as upgrade_date<br>FROM subscriptions<br>WHERE plan_id = 3)<br>SELECT CONCAT(CEIL(AVG(DATEDIFF(T1.upgrade_date, s.start_date))), &#39; days&#39;) AS avg_upgrade_time<br>FROM T1<br>JOIN subscriptions as s<br>ON T1.customer_id = s.customer_id<br>WHERE plan_id = 0;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/140/1*yBRSoADC5ODhcXOOXAKfSQ.png" /><figcaption><strong>On average, it takes a customer 105 days (over 3 months) to subscribe to an annual plan</strong></figcaption></figure><p>10. Can you further breakdown this average value into 30 day periods (i.e. 0–30 days, 31–60 days etc)?</p><p><strong>Solution:</strong> Including the CASE statement, define the upgrade categories and order the categories by upgrade time.</p><pre>WITH T1 AS (SELECT customer_id, start_date as upgrade_date<br>FROM subscriptions<br>WHERE plan_id = 3),<br>T2 AS (SELECT T1.customer_id, DATEDIFF(T1.upgrade_date, s.start_date) AS upgrade_time,<br>CASE WHEN DATEDIFF(T1.upgrade_date, s.start_date) BETWEEN 0 AND 30 THEN &#39;0-30 days&#39;<br> WHEN DATEDIFF(T1.upgrade_date, s.start_date) BETWEEN 31 AND 60 THEN &#39;31-60 days&#39; <br>    WHEN DATEDIFF(T1.upgrade_date, s.start_date) BETWEEN 61 AND 90 THEN &#39;61-90 days&#39;<br>    WHEN DATEDIFF(T1.upgrade_date, s.start_date) BETWEEN 91 AND 120 THEN &#39;91-120 days&#39;<br>    WHEN DATEDIFF(T1.upgrade_date, s.start_date) BETWEEN 121 AND 150 THEN &#39;121-150 days&#39;<br>    WHEN DATEDIFF(T1.upgrade_date, s.start_date) BETWEEN 151 AND 180 THEN &#39;151-180 days&#39; <br>    WHEN DATEDIFF(T1.upgrade_date, s.start_date) BETWEEN 181 AND 210 THEN &#39;181-210 days&#39; <br>    WHEN DATEDIFF(T1.upgrade_date, s.start_date) BETWEEN 211 AND 240 THEN &#39;211-240 days&#39; <br>    WHEN DATEDIFF(T1.upgrade_date, s.start_date) BETWEEN 241 AND 270 THEN &#39;241-270 days&#39;<br>    WHEN DATEDIFF(T1.upgrade_date, s.start_date) BETWEEN 271 AND 300 THEN &#39;271-300 days&#39; <br>    WHEN DATEDIFF(T1.upgrade_date, s.start_date) BETWEEN 301 AND 330 THEN &#39;301-330 days&#39;<br>    WHEN DATEDIFF(T1.upgrade_date, s.start_date) BETWEEN 311 AND 360 THEN &#39;311-360 days&#39;END AS upgrade_category<br>FROM T1<br>JOIN subscriptions as s<br>ON T1.customer_id = s.customer_id<br>WHERE plan_id = 0)<br><br>SELECT T2.upgrade_category, COUNT(*) as no_of_customers, FLOOR(AVG(T2.upgrade_time)) as avg_upg_time<br>FROM T2<br>GROUP BY 1<br>ORDER BY T2.upgrade_time;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/368/1*2Oxf1-3OIbT8gvxd49iPjw.png" /><figcaption><strong>Days to upgrade increase steadily till the 121–150 day mark, and there’s a steady decline, with minimal customer activity from 271 days.</strong></figcaption></figure><p>11. How many customers downgraded from a pro monthly to a basic monthly plan in 2020?</p><p><strong>Solution:</strong> Using a common table expression including the start_date as the date of upgrade to pro monthly plan and filtering for entries where plan_id is 2 (pro monthly), count the number of entries where the pro monthly upgrade date comes before the basic monthly date. Join the CTE to the subscriptions table on the customer_id column and filter for year 2020.</p><pre>WITH T2 AS (SELECT customer_id, start_date as pro_monthly_date<br>FROM subscriptions<br>WHERE plan_id = 2)<br>SELECT COUNT(CASE WHEN T2.pro_monthly_date &lt; s.start_date THEN &#39;downgrade&#39; END) AS no_of_downgrades<br>FROM T2<br>JOIN subscriptions as s<br>ON T2.customer_id = s.customer_id<br>WHERE plan_id = 1 AND YEAR(T2.pro_monthly_date) = 2020;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/137/1*x0cWKH0GWzMegWHmUd08Bg.png" /><figcaption><strong>No customer downgraded</strong></figcaption></figure><h4>Challenge Payment Question</h4><p>The Foodie-Fi team wants you to create a new payments table for the year 2020 that includes amounts paid by each customer in the subscriptions table with the following requirements:</p><ul><li>monthly payments always occur on the same day of month as the original start_date of any monthly paid plan</li><li>upgrades from basic to monthly or pro plans are reduced by the current paid amount in that month and start immediately</li><li>upgrades from pro monthly to pro annual are paid at the end of the current billing period and also starts at the end of the month period</li><li>once a customer churns they will no longer make payments</li></ul><p>The code to do this is rather long, so I’ll only include the last part of it here:</p><pre>-- Insert data into payments table<br>SELECT <br>    customer_id,<br>    plan_id,<br>    plan_name,<br>    payment_date,<br>    amount,<br>    RANK() OVER(PARTITION BY customer_id ORDER BY customer_id, plan_id, payment_date) AS payment_order<br>FROM Date_CTE<br>WHERE YEAR(payment_date) = 2020<br>ORDER BY customer_id, plan_id, payment_date;<br><br>SELECT * FROM payments;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/547/1*FOPpqBK9kSrQ2KBhLRqvWQ.png" /><figcaption><strong>A section through the payments table</strong></figcaption></figure><p><em>Answers to the Outside-the-box questions will be included and updated subsequently in the GitHub </em><a href="https://github.com/Neto-A/8-Week-SQL-Challenge/tree/main/Foodie%20Fi"><em>folder</em></a><em> that contains my full solutions!</em></p><h4>FINAL WORDS</h4><p>This was a much more practical challenge, as it focused more on real business situations than on script. I was introduced to Recursion in SQL. I’ll definitely be searching for more use cases for recursive CTEs. Looking forward to Case Study 4!</p><p>As always, I am open to questions and corrections on this project and collaborations on future projects.</p><p>Till my next article, <em>¡adiós!</em></p><p>PS: Follow me on <a href="https://twitter.com/Naeto__">Twitter</a> and connect with me on <a href="https://linkedin.com/in/stephanieanyama">LinkedIn</a>. View my portfolio <a href="https://stephanieanyama.carrd.co/">here</a>. All code used and the link to the interactive report are stored in the Foodie Fi <a href="https://github.com/Stephanie-Anyama/8-Week-SQL-Challenge/tree/main/Foodie%20Fi">folder</a> on my GitHub.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=582bf2ebf230" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[I Ran Pizzas and Queries — Here’s What I Delivered]]></title>
            <link>https://medium.com/@stephanie_a/i-ran-pizzas-and-queries-heres-what-i-delivered-63f7858709dd?source=rss-8bc50dcf814a------2</link>
            <guid isPermaLink="false">https://medium.com/p/63f7858709dd</guid>
            <category><![CDATA[danny-ma]]></category>
            <category><![CDATA[data-analytics]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[sql]]></category>
            <dc:creator><![CDATA[Stephanie Anyama]]></dc:creator>
            <pubDate>Mon, 21 Aug 2023 12:02:29 GMT</pubDate>
            <atom:updated>2023-08-21T12:02:29.366Z</atom:updated>
            <content:encoded><![CDATA[<h3>I Ran Pizzas and Queries — Here’s What I Delivered</h3><h4>Case Study 2 of Danny Ma’s #8WeekSQLChallenge</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/535/1*uLETIxCrLRoxevDXkRd_ag.png" /></figure><h4>INTRODUCTION</h4><p>This is the <a href="https://8weeksqlchallenge.com/case-study-2/">second</a> installment of my <strong>#8WeekSQLChallenge</strong> series by Danny Ma. I’ll be honest, this challenge was in no way easy. It tested my previous knowledge, and to a large extent, my research skills. There was a lot to clean within the provided datasets, and many case study questions to answer.</p><p>This is going to be a long article, and you might get hungry. You might want to order a large Meatlovers pizza from Pizza Runner for this one. Let’s get into it.</p><h4>THE PROBLEM</h4><p>Danny launched Pizza Runner to get in on a massive pizza market and so, recruited “runners” to deliver fresh pizza from Pizza Runner Headquarters (his house). He requires assistance to clean his data and apply some basic calculations so he can better direct his runners and optimize Pizza Runner’s operations.</p><h4>THE DATA</h4><p>Danny provided SIX datasets, all of which exist within the pizza_runner database schema:</p><ul><li>customer_orders: Captured in the customer_orders table are 1 row for each individual pizza that is part of the order, the pizza_id (the type of pizza which was ordered) &amp; the exclusions and extras which are the ingredient_id values which should be removed from the pizza and those which need to be added to the pizza respectively.</li><li>runners : This shows the registration_date and runner_id for each new runner.</li><li>runner_orders : This contains the pickup_time (timestamp at which the runner picks 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.</li><li>pizza_names: There are only 2 pizza options: Meat Lovers and Vegetarian.</li><li>pizza_recipes: This contains pizza_id and toppings, showing the ingredients used for making each pizza.</li><li>pizza_toppings: These shows the name of every ingredient used to make pizzas, irrespective of type, sorted by topping_id.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/674/1*1m9pKbTEr8BTUtBl04_y-Q.png" /><figcaption>The Entity Relationship Diagram (ERD)</figcaption></figure><h4>DATA CLEANING</h4><p>These datasets were in some way or another rife with quality and tidiness issues, which needed to be cleaned before embarking on the data exploration. Remember, cleaning is an iterative process, so sometimes you might have to go back and correct some things, and that’s okay.</p><p>Here are the issues I cleaned:</p><ol><li>Null and empty rows in the runner_orders.cancellation column.</li><li>Null and empty rows in the customer_orders.exclusions column.</li><li>Null and empty rows in the customer_orders.extras column.</li><li>Unnecessary spaces, with the ‘km’ unit in the runner_orders.distance column.</li><li>Unnecessary spaces, with the ‘mins’ and ‘minutes’ units in the runner_orders.duration column.</li><li>Unnecessary spaces in the pizza_recipes.toppings , customer_orders. exclusions, and customer_orders.extras columns.</li></ol><pre>-- to change null and empty rows in the cancellation column to Not Cancelled<br>UPDATE runner_orders set cancellation=&#39;Not Cancelled&#39;<br>WHERE cancellation != &#39;Restaurant Cancellation&#39; AND cancellation != &#39;Customer Cancellation&#39; OR cancellation IS NULL;<br><br>-- to change null and empty rows in the exclusions column to None<br>UPDATE customer_orders set exclusions=&#39;None&#39;<br>WHERE exclusions = &#39;null&#39; OR exclusions = &#39;&#39; OR exclusions IS NULL;<br><br>-- to change null and empty rows in the extras column to None<br>UPDATE customer_orders set extras=&#39;None&#39;<br>WHERE extras = &#39;null&#39; OR extras = &#39;&#39; OR extras IS NULL;<br><br>-- to clean the spaces and remove the &#39;km&#39; in the distance column<br>UPDATE runner_orders SET distance = REPLACE(distance, &#39; &#39;, &#39;&#39;);<br>UPDATE runner_orders SET distance = REPLACE(distance, distance, LEFT(distance, LENGTH(distance) - 2));<br>UPDATE runner_orders SET distance = 0<br>WHERE distance = &#39;&#39; OR distance = &#39;nu&#39;;<br><br>-- to clean the spaces and remove the &#39;mins&#39; and &#39;minutes&#39; in the distance column<br>UPDATE runner_orders SET duration = REPLACE(duration, duration, LEFT(duration, 2));<br>UPDATE runner_orders SET duration = 0<br>WHERE duration = &#39;nu&#39;;<br><br>-- to remove extra spaces in the toppings column<br>UPDATE pizza_recipes SET toppings = REPLACE(toppings, &#39; &#39;, &#39;&#39;);<br><br>-- to remove extra spaces in the extras column<br>UPDATE customer_orders SET extras = REPLACE(extras, &#39; &#39;, &#39;&#39;);<br><br>-- to remove extra spaces in the exclusions column<br>UPDATE customer_orders SET exclusions = REPLACE(exclusions, &#39; &#39;, &#39;&#39;);</pre><h4>DATA EXPLORATION</h4><p>This case study came in 5 parts under different headings, with their own questions: Pizza Metrics, Runner and Customer Experience, Ingredient Optimisation, Pricing and Ratings, Bonus Question.</p><p>To avoid making this article too long, I’ll only write about the first 3 questions per part, and the Bonus Question.</p><p><strong>Pizza Metrics</strong></p><ol><li>How many pizzas were ordered?</li></ol><pre>SELECT COUNT(order_id) as no_ordered<br>FROM customer_orders;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/223/1*_lraWRzDh-DJ_p12UB2L-w.png" /><figcaption><strong>There were 14 pizzas ordered in total (irrespective of outcome)</strong></figcaption></figure><p>2. How many unique customer orders were made?</p><pre>SELECT COUNT(DISTINCT order_id) as unique_customer_orders<br>FROM customer_orders;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/413/1*NqfVzFPpn0iUZUTqrMiudg.png" /><figcaption><strong>There were 10 unique customer orders</strong></figcaption></figure><p>3. How many successful orders were delivered by each runner?</p><pre>SELECT runner_id, COUNT(cancellation) as sus_deliveries<br>FROM runner_orders<br>WHERE cancellation = &#39;Not Cancelled&#39;<br>GROUP BY 1<br>ORDER BY 2 DESC;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/230/1*P5cXU-fFpZ5EniZieXPUOw.png" /><figcaption><strong>Runner 1 made 4 successful deliveries, 3 for Runner 2 and 1 for Runner 3</strong></figcaption></figure><p><strong>Runner and Customer Experience</strong></p><ol><li>How many runners signed up for each 1 week period? (i.e. week starts 2021-01-01)</li></ol><pre>SELECT WEEK(registration_date) as week_number,<br>COUNT(runner_id) runners_signed<br>FROM runners<br>GROUP BY WEEK(registration_date)<br>ORDER BY WEEK(registration_date);</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/223/1*kTBFb0qFIWLelufxa1mFgA.png" /><figcaption><strong>In the first week (0), only 1 runner was registered</strong></figcaption></figure><p>2. What was the average time in minutes it took for each runner to arrive at the Pizza Runner HQ to pickup the order?</p><pre>SELECT CONCAT(AVG(duration), &#39; minutes&#39;) AS avg_pickup_time<br>FROM runner_orders<br>WHERE duration != 0;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/220/1*J14UFTGs3d9Q0TA1HuQ-aA.png" /><figcaption><strong>It took an average of 23 minutes for the runners to pick up their orders</strong></figcaption></figure><p>3. Is there any relationship between the number of pizzas and how long the order takes to prepare?</p><pre>SELECT c.order_id, COUNT(c.pizza_id) as no_delivered, r.duration<br>FROM customer_orders as c<br>JOIN runner_orders as r<br>ON c.order_id = r.order_id<br>WHERE r.cancellation = &#39;Not Cancelled&#39;<br>GROUP BY 1<br>ORDER BY 2 DESC;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/303/1*Ybx61C4MvzneheS7Y5xOzA.png" /><figcaption><strong>As seen in this table, preparation time increases with larger orders</strong></figcaption></figure><p><strong>Ingredient Optimisation</strong></p><ol><li>What are the standard ingredients for each pizza?</li></ol><pre>SELECT p.pizza_name, <br>  GROUP_CONCAT(distinct(t.topping_name)) AS standard_ingredients<br>FROM pizza_names p<br>JOIN pizza_recipes r ON p.pizza_id = r.pizza_id<br>JOIN pizza_toppings t ON FIND_IN_SET(t.topping_id, r.toppings)<br>GROUP BY p.pizza_name;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/776/1*OzAiN52sZ9zXhT8wK74qvw.png" /></figure><p>2. What was the most commonly added extra?</p><pre>SELECT topping_name AS most_common_extra,<br>    COUNT(*) AS extra_count<br>FROM customer_orders<br>JOIN pizza_toppings ON FIND_IN_SET(topping_id, extras)<br>WHERE extras != &#39;None&#39;<br>GROUP BY topping_id<br>ORDER BY extra_count DESC<br>LIMIT 1;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/394/1*96FUd5XHQSERpB800vcITA.png" /></figure><p>3. What was the most common exclusion?</p><pre>SELECT topping_name AS most_common_exclusion,<br>    COUNT(*) AS exclusion_count<br>FROM customer_orders<br>JOIN pizza_toppings ON FIND_IN_SET(topping_id, exclusions)<br>WHERE exclusions != &#39;None&#39;<br>GROUP BY topping_id<br>ORDER BY exclusion_count DESC<br>LIMIT 1;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/285/1*usn9AvADyij9REGfx8KdFQ.png" /></figure><p><strong>Pricing and Ratings</strong></p><ol><li>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?</li></ol><pre>SELECT CONCAT(&#39;$&#39;, <br>  SUM(CASE WHEN c.pizza_id = 1 THEN 12<br>  WHEN c.pizza_id = 2 THEN 10 END)) AS total_earnings<br>FROM customer_orders as c<br>JOIN runner_orders as r<br>ON c.order_id = r.order_id<br>WHERE r.cancellation = &#39;Not Cancelled&#39;;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/184/1*bC8gBUpxx-xRk9sTjbIUug.png" /><figcaption><strong>Pizza Runner earned $138 dollars, excluding delivery fees</strong></figcaption></figure><p>2. What if there was an additional $1 charge for any pizza extras?</p><pre>SELECT CONCAT(&#39;$&#39;, (SELECT  <br>  SUM(CASE WHEN c.pizza_id = 1 THEN 12<br>  WHEN c.pizza_id = 2 THEN 10 END) AS total_earnings<br>FROM customer_orders as c<br>JOIN runner_orders as r<br>ON c.order_id = r.order_id<br>WHERE r.cancellation = &#39;Not Cancelled&#39;) + <br>(SELECT<br>    COUNT(*) AS extra_count<br>FROM customer_orders<br>JOIN pizza_toppings ON FIND_IN_SET(topping_id, extras)<br>WHERE extras != &#39;None&#39;)) AS gross_earnings;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/214/1*VQZtzHjubG_V6RLPvu5oVw.png" /><figcaption><strong>With $1 for each extra, total earnings came to $144</strong></figcaption></figure><p>3. 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_ratings;<br><br>CREATE TABLE runner_ratings AS<br>SELECT order_id,<br>    runner_id,<br>       CASE WHEN cancellation != &#39;Not Cancelled&#39; THEN &#39;-&#39; <br>       WHEN duration &lt;= 15 THEN 5<br>       WHEN duration &gt;15 AND duration &lt;= 25 THEN 4<br>       WHEN duration &gt;25 AND duration &lt;= 35 THEN 3<br>       WHEN duration &gt;35 AND duration &lt;= 45 THEN 2<br>       WHEN duration &gt;45 THEN 1<br> END AS ratings<br>FROM runner_orders;<br><br>SELECT * FROM runner_ratings;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/212/1*-eBJPbq-1clCfCzaVaZ5Dw.png" /><figcaption><strong>The Ratings Table</strong></figcaption></figure><p><strong>Bonus Question:</strong> Write an INSERT statement to demonstrate what would happen if a new Supreme pizza with all the toppings was added to the Pizza Runner menu?</p><pre>-- to include the new pizza type into the pizza_names table<br>INSERT INTO pizza_names<br>VALUES (3, &#39;Supreme&#39;);<br>SELECT * FROM pizza_names;<br><br>-- to include the Supreme pizza recipe<br>INSERT INTO pizza_recipes <br>SELECT p.pizza_id, GROUP_CONCAT(t.topping_id)<br>FROM pizza_names as p, pizza_toppings as t<br>WHERE p.pizza_name = &#39;Supreme&#39;;</pre><h4>FINAL WORDS</h4><p>This was a very tricky, yet highly rewarding, challenge. I improved on my use of basic joins and subqueries, and learned to use the GROUP_CONCAT function to work through comma separated values. I also familiarized myself with using more tables than I’d previously been exposed to. I appreciated using this case study to get extra practice. Looking forward to Case Study 3!</p><p>As always, I am open to questions and corrections on this project and collaborations on future projects.</p><p>Till my next article, <em>¡adiós!</em></p><p>PS: Follow me on <a href="https://twitter.com/Naeto__">Twitter</a> and connect with me on <a href="https://www.linkedin.com/in/stephanie-netoafomachukwu-anyama-0b5257204">LinkedIn</a>. View my portfolio <a href="https://stephanieanyama.carrd.co/">here</a>. All code used and the link to the interactive report are stored in the Pizza Runner <a href="https://github.com/Neto-A/8-Week-SQL-Challenge/blob/main/Pizza%20Runner/Pizza%20Runner.sql">folder</a> on my GitHub.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=63f7858709dd" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[I Took a Job at Danny’s Diner — Here’s How I Did]]></title>
            <link>https://medium.com/@stephanie_a/i-took-a-job-at-dannys-diner-here-s-how-i-did-1a0aa8baab6b?source=rss-8bc50dcf814a------2</link>
            <guid isPermaLink="false">https://medium.com/p/1a0aa8baab6b</guid>
            <category><![CDATA[danny-ma]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[mysql]]></category>
            <dc:creator><![CDATA[Stephanie Anyama]]></dc:creator>
            <pubDate>Mon, 14 Aug 2023 11:02:22 GMT</pubDate>
            <atom:updated>2025-02-19T22:16:27.905Z</atom:updated>
            <content:encoded><![CDATA[<h3>I Took a Job at Danny’s Diner — Here’s How I Did</h3><h4>Case Study 1 of Danny Ma’s <strong>#8WeekSQLChallenge</strong></h4><h4>INTRODUCTION</h4><p>It is general knowledge that SQL is one of the most important tools in every data analyst’s toolbox. I, for one, needed to learn and practice using it to solve real problems as I’ve been lax about it. And so, over the past few weeks, I’ve been taking an SQL course on Udacity, and practising interview questions and challenges on <a href="https://datalemur.com/questions">Datalemur</a> and <a href="https://www.hackerrank.com/domains/sql">Hackerrank</a>.</p><p>In my latest attempt at improving on using SQL to complete real projects, I joined Danny Ma’s <strong>#8WeekSQLChallenge, </strong>with other Women in Data. This is the <a href="https://8weeksqlchallenge.com/case-study-1/">first</a> of 8 case studies, all of which I shall be working and writing about.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/556/1*4Qx79HjsC-bIDs6vg5ug5Q.png" /></figure><p>So, I took a job at Danny’s Diner — here’s how that went.</p><h4>PROBLEM STATEMENT</h4><p>Danny opened a restaurant in 2021 to satisfy his love for Japanese food and has gathered some data to understand the trends in his budding business. He wanted to use my solutions to understand the current state of his ongoing loyalty program, as well as create tables to enable him understand the data at a glance, without using SQL.</p><h4>THE DATA</h4><p>Danny provided 3 datasets: sales, menu and members. All 3 tables exist within the dannys_diner schema.</p><ul><li>sales: This contains all customer_id level purchases with the corresponding order_date and product_id information for when and what menu items were ordered.</li><li>menu: This table shows the product_id with its actual product_name and price.</li><li>members: captures the join_date when a customer_id joined the beta version of the Danny’s Diner loyalty program. There are only customers which are also members, A &amp; B.</li></ul><p>Below is the entity relationship diagram (ERD) showing the relationships among the tables:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/544/1*gmwL72N0nrpbHgZcS5lqbw.png" /></figure><h4>SOLUTIONS</h4><p>These queries were written and executed in the MySQL relational database management system.</p><p><strong>What is the total amount each customer spent at the restaurant?</strong></p><p>Solution:</p><pre>SELECT s.customer_id customer, CONCAT(&#39;$&#39;, SUM(m.price)) total_spend<br>FROM sales as s<br>JOIN menu as m<br>ON s.product_id = m.product_id<br>GROUP BY 1<br>ORDER BY 2 DESC;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/268/1*Ais6bMWBAxf2lwzntUEU2Q.png" /></figure><p>Customers A, B and C spent $76, $74 and $36 respectively.</p><p><strong>How many days has each customer visited the restaurant?</strong></p><p>Solution:</p><pre>SELECT customer_id customer, <br>CONCAT(COUNT(DISTINCT order_date), &#39; &#39;, &#39;days&#39;) visit_date<br>FROM sales<br>GROUP BY 1<br>ORDER BY 2 DESC;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/305/1*h25PqhnnETIiz9jS8PfqdQ.png" /></figure><p>Customers A, B and C have visited the restaurant on 4, 6 and 2 <strong>distinct</strong> occasions respectively.</p><p><strong>What was the first item from the menu purchased by each customer?</strong></p><p>Solution:</p><pre>SELECT customer, product<br>FROM <br>(SELECT s.customer_id customer,<br>s.order_date, m.product_name as product, <br>ROW_NUMBER() OVER(PARTITION BY s.customer_id ORDER BY s.order_date) AS row_num<br>FROM sales as s<br>JOIN menu as m<br>ON s.product_id = m.product_id) temp_table<br>WHERE row_num = 1;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/210/1*KNecMx-Ow19fVmBIXsJ6rg.png" /></figure><p>Customers A, B and C’s first item purchased were sushi, curry and ramen respectively.</p><p><strong>What is the most purchased item on the menu and how many times was it purchased by all customers?</strong></p><p>Solution:</p><pre>SELECT m.product_name, COUNT(s.product_id) no_of_purchases, CONCAT(&#39;$&#39;, SUM(m.price)) total_sales<br>FROM sales as s<br>JOIN menu as m<br>ON s.product_id = m.product_id<br>GROUP BY s.product_id<br>ORDER BY 3 DESC<br>LIMIT 1;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/646/1*92V6828DqRPljppvtLc5dw.png" /></figure><p>Ramen was the most purchased item. It was sold 8 times and made $96 in revenue.</p><p><strong>Which item was the most popular for each customer?</strong></p><p>Solution:</p><pre>SELECT customer, product_name, no_of_purchases FROM<br>(SELECT s.customer_id customer, m.product_name, COUNT(s.product_id) no_of_purchases,<br>DENSE_RANK() OVER(PARTITION BY s.customer_id ORDER BY COUNT(s.product_id) DESC) AS pop_purchase<br>FROM sales AS s<br>JOIN menu AS m<br>ON s.product_id = m.product_id<br>GROUP BY 1, s.product_id) T1<br>WHERE pop_purchase = 1;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/335/1*1nS74tZMyHaR3FozY_13Zg.png" /></figure><p>Customer A and C’s most popular product was ramen. For Customer B, all products on the menu were equally popular.</p><p><strong>Which item was purchased first by the customer after they became a member?</strong></p><p>Solution:</p><pre>SELECT customer, product_name<br>FROM<br>(SELECT s.customer_id customer, m.product_name, s.order_date,<br>ROW_NUMBER() OVER(PARTITION BY s.customer_id ORDER BY order_date) AS row_num<br>FROM menu as m<br>JOIN sales as s<br>ON s.product_id = m.product_id<br>JOIN members as mem<br>ON mem.customer_id = s.customer_id<br>WHERE mem.join_date &lt;= s.order_date<br>ORDER BY 1, 3) T2<br>WHERE row_num = 1;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/292/1*mSnjpgifwA90qxbI_Y_9nw.png" /></figure><p><strong>Which item was purchased just before the customer became a member?</strong></p><p>Solution:</p><pre>SELECT customer, product_name<br>FROM<br>(SELECT s.customer_id customer, m.product_name, s.order_date,<br>ROW_NUMBER() OVER(PARTITION BY s.customer_id ORDER BY order_date DESC) AS row_num<br>FROM menu as m<br>JOIN sales as s<br>ON s.product_id = m.product_id<br>JOIN members as mem<br>ON mem.customer_id = s.customer_id<br>WHERE mem.join_date &gt; s.order_date<br>ORDER BY 1, 3) T3<br>WHERE row_num = 1;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/253/1*WlbfxIgYdS_yk-wesFH0-A.png" /></figure><p>Both customers A &amp; B purchased sushi just before becoming members.</p><p><strong>What is the total items and amount spent for each member before they became a member?</strong></p><p>Solution:</p><pre>SELECT s.customer_id customer, COUNT(s.product_id) purchases, <br>CONCAT(&#39;$&#39;, SUM(m.price)) amt_spent<br>FROM menu as m<br>JOIN sales as s<br>ON s.product_id = m.product_id<br>JOIN members as mem<br>ON mem.customer_id = s.customer_id<br>WHERE mem.join_date &gt; s.order_date<br>GROUP BY 1<br>ORDER BY 2,3 DESC;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/497/1*krIAcRHKK9sexro17_6maw.png" /></figure><p>Customer A made 2 purchases and spent $25 before becoming a member, while Customer B made 3purchases and spent $40.</p><p><strong>If each $1 spent equates to 10 points and sushi has a 2x points multiplier — how many points would each customer have?</strong></p><p>Solution:</p><pre>SELECT customer, CONCAT(SUM(points), &#39; &#39;, &#39;points&#39;) total_tally<br>FROM<br>(SELECT s.customer_id customer, s.product_id, m.product_name, m.price,<br>CASE WHEN m.product_name = &#39;sushi&#39; THEN price * 20<br>ELSE price * 10 END AS points<br>FROM sales as s<br>JOIN menu as m<br>ON s.product_id = m.product_id) T4<br>GROUP BY customer;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/208/1*8FbcI3Ya-8nGAgF9qzribA.png" /></figure><p><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?</strong></p><p>Solution:</p><pre>SELECT customer, CONCAT(SUM(point_tally), &#39;points&#39;) total_tally<br>FROM<br>(SELECT s.customer_id customer, m.product_name, s.order_date,<br>CASE WHEN s.order_date &lt; (mem.join_date + 7) THEN m.price * 20<br>ELSE m.price * 10 END AS point_tally<br>FROM menu as m<br>JOIN sales as s<br>ON s.product_id = m.product_id<br>JOIN members as mem<br>ON mem.customer_id = s.customer_id<br>WHERE mem.join_date &lt; s.order_date AND month(s.order_date) = 1<br>ORDER BY 1, 3)T5<br>GROUP BY customer<br>ORDER BY customer;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/284/1*uMDuWRjOf1QAMOO3qNdbGQ.png" /></figure><p><strong>Bonus Question 1: </strong>Using joins, I was expected to create a table containing all the information across all tables, including a column showing whether each purchase was made at the same time the customer was part of the loyalty program.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/393/1*BkZGkdlzk08PzzEzibupPg.png" /></figure><p>Here’s how I did it:</p><pre>SELECT s.customer_id customer, s.order_date, m.product_name, m.price,<br>CASE WHEN s.order_date &gt;= mem.join_date AND s.customer_id = mem.customer_id THEN &#39;Y&#39;<br>ELSE &#39;N&#39; END AS member<br>FROM menu as m<br>LEFT JOIN sales as s<br>ON s.product_id = m.product_id<br>LEFT JOIN members as mem<br>ON mem.customer_id = s.customer_id<br>ORDER BY 1, 2;</pre><p><strong>Bonus Question 2:</strong> Danny also required a ranking of all purchases made by order date, but specifically for member-only purchases. Other purchases were to be left as null entries. Below is the table I was to recreate:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/427/1*EvaTNP6VTqSVF5-y_MqeFQ.png" /></figure><p>My solution:</p><pre>SELECT s.customer_id customer, s.order_date, m.product_name, m.price,<br>CASE WHEN s.order_date &gt;= mem.join_date AND s.customer_id = mem.customer_id <br>THEN &#39;Y&#39; ELSE &#39;N&#39; END AS member,<br>CASE WHEN s.order_date &gt;= mem.join_date AND s.customer_id = mem.customer_id <br>THEN DENSE_RANK() OVER(PARTITION BY s.customer_id, order_date &gt;=mem.join_date <br>      ORDER BY s.order_date) <br>ELSE &#39;null&#39; END AS ranking<br>FROM menu as m<br>LEFT JOIN sales as s<br>ON s.product_id = m.product_id<br>LEFT JOIN members as mem<br>ON mem.customer_id = s.customer_id<br>ORDER BY 1, 2;</pre><h4>FINAL WORDS.</h4><p>This is my first SQL project in a long time. I learned how to utilize basic joins, subqueries, and window functions to carry out important tasks. Understanding window functions can be particularly difficult, thus I appreciated using this case study to get extra practice. I’m definitely anticipating Case Study 2.</p><p>As always, I am open to questions and corrections on this project and collaborations on future projects.</p><p>Till my next article, <em>¡adiós!</em></p><p>PS: Follow me on <a href="https://twitter.com/Naeto__">Twitter</a> and connect with me on <a href="https://linkedin.com/in/stephanieanyama">LinkedIn</a>. View my portfolio <a href="https://stephanieanyama.carrd.co/">here</a>. All code used and the link to the interactive report are stored in the Danny’s Diner SQL <a href="https://github.com/Neto-A/8-Week-SQL-Challenge/blob/main/Danny&#39;s%20Diner/Danny&#39;s%20Diner.sql">folder</a> on my GitHub.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=1a0aa8baab6b" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[A Data-Driven Guide to Your Next Read]]></title>
            <link>https://medium.com/@stephanie_a/a-data-driven-guide-to-your-next-read-8b6b05e73f12?source=rss-8bc50dcf814a------2</link>
            <guid isPermaLink="false">https://medium.com/p/8b6b05e73f12</guid>
            <category><![CDATA[amazon]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[data-analytics]]></category>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[python]]></category>
            <dc:creator><![CDATA[Stephanie Anyama]]></dc:creator>
            <pubDate>Mon, 05 Jun 2023 13:01:02 GMT</pubDate>
            <atom:updated>2023-06-05T13:01:02.219Z</atom:updated>
            <content:encoded><![CDATA[<h4>A Review of Amazon’s Bestsellers from 2009 to 2019</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/506/1*RjcrCF1SevxjKzPWrTvX5g.png" /><figcaption>Amazon Books</figcaption></figure><h4>INTRODUCTION</h4><p>If you’re a voracious pleasure reader, you will agree — and probably nod with a wide smile — that nothing really compares to the feeling of turning pages of a printed book. Collecting books is every reader’s pride, and to this end, Amazon has made this possible and affordable.</p><p>Amazon is the largest online marketplace for print books worldwide. The company controls the majority of the print book market, with the estimates that the company’s share in the market will only grow in the future. It is said that in the USA alone, Amazon sells a whopping <em>300 million copies</em> per year. Each year, certain books are the most purchased and enjoyed by customers.</p><p>In this project, I took up the challenge of finding out what factors guided the purchase of the most popular books, ranging from price point to cover type, with everything in between. So, get comfortable, as you would if you were with your favourite book, and read this review.</p><p><strong><em>Features of the Dataset.</em></strong></p><ol><li>price: The retail price for the item on Amazon.</li><li>ranks: The rank of the book in its bestsellers list for its year.</li><li>title: The title of the book.</li><li>average_rating: The average rating given to each book on a scale of 1–5.</li><li>no_of_reviews: The number of reviews posted by readers and customers on Amazon.</li><li>author: The name of the author.</li><li>cover type: The type of cover used for each book, such as Hardback, Paperback etc.</li><li>year: The year the book made the bestsellers’ list.</li><li>genre: The genre of each book which could either be fiction or non-fiction.</li></ol><h4>PRELIMINARY WRANGLING</h4><h4><strong>Gathering.</strong></h4><p>To gather the data used for this project, I had to scrape the Amazon website containing the <a href="https://www.amazon.com/gp/bestsellers/2010/books">bestsellers’ list</a> for each year. I did this using Python, specifically the Beautiful Soup, requests and numpy libraries. Given that I was scraping using the Chrome browser, I also downloaded and utilized the Chrome webdriver library from Selenium, which is tailored for Python.</p><pre>import pandas as pd<br>import numpy as np<br>import seaborn as sb<br>import matplotlib.pyplot as plt<br><br>import requests<br>from bs4 import BeautifulSoup<br>from selenium import webdriver<br>from webdriver_manager.chrome import ChromeDriverManager <br>from selenium.webdriver.chrome.service import Service as ChromeService<br>from urllib.request import urlopen<br>from time import sleep<br><br>import warnings<br>warnings.filterwarnings(&#39;ignore&#39;)</pre><p>It is important to note that, while specifiying the range of years to be considered, I had to add 2020, as the webdriver excludes the last year stated. I will be honest, not knowing this at first frustrated me, because 2019 was consistently excluded from my scraped data.</p><pre>#since the first page and last page has the same pattern url for all the years <br>urls =[]<br>years = [str(i) for i in range(2009,2020)] #list of years between 2009-2020<br>for year in years:<br>    urls.append (f&quot;https://www.amazon.com/gp/bestsellers/{year}/books&quot;)<br>    urls.append(f&quot;https://www.amazon.com/gp/bestsellers/{year}/books/ref=zg_bsar_pg_2/ref=zg_bsar_pg_2?ie=UTF8&amp;pg=2&quot;)<br><br>def get_dir(book,year): <br>    &#39;&#39;&#39;to get the details of each book for each year&#39;&#39;&#39; <br>    <br>    import numpy as np<br>    &#39;&#39;&#39;to get the name of price&#39;&#39;&#39;<br><br>    try:<br>        price = book.find(&#39;span&#39;,class_=&quot;_cDEzb_p13n-sc-price_3mJ9Z&quot;).text[1:]<br>    except Exception as e:<br>        price = np.nan<br>    try:<br>        ranks = book.find(&#39;span&#39;, class_=&#39;zg-bdg-text&#39;).text[1:]<br>    except Exception as e:<br>        ranks = np.nan<br>    try:<br>        title = book.find(&#39;div&#39;,class_=&quot;_cDEzb_p13n-sc-css-line-clamp-1_1Fn1y&quot;).text<br>    except Exception as e:<br>        title = np.nan<br>    try:<br>        ratings= book.find(&#39;span&#39;,class_=&quot;a-icon-alt&quot;).text[:3] <br>    except Exception as e:<br>        ratings = np.nan<br>    try:<br>        no_of_reviews  = book.find(&#39;span&#39;,class_=&quot;a-size-small&quot;).text<br>    except Exception as e:<br>        no_of_reviews = np.nan<br>    try:<br>        author = book.find(&#39;a&#39;,class_=&quot;a-size-small a-link-child&quot;).text<br>    except Exception as e:<br>        author = np.nan<br>    try:<br>        cover_type = book.find(&#39;span&#39;,class_=&quot;a-size-small a-color-secondary a-text-normal&quot;).text<br>    except Exception as e:<br>        cover_type = np.nan<br>    year = year<br>    return [price,ranks,title,ratings,no_of_reviews,author,cover_type, year]</pre><blockquote>For ease of following along, I have only shown a section of the code used. The rest of it can be found in this <a href="https://github.com/Neto-A/Amazon-Bestsellers-2009-2019-/blob/main/Amazon%20Bestsellers%20(2009-2019).ipynb">notebook</a>.</blockquote><p>I read the information obtained into a data dictionary, and then into a dataframe. Unfortunately, the web pages did not provide information on the genre used. Therefore, I sought help from an earlier <a href="https://github.com/Chisomnwa/Amazon-Best-Selling-Books-Analysis/blob/main/Part_1_Amazon_Web_Scrapping.ipynb">project</a>, which used information from Goodreads.</p><p>The completed dataframe was saved as a .csv file, for future assessment and cleaning.</p><h4><strong>Assessing.</strong></h4><p>As is characteristic of any data analytics project, I carried out visual and programmatic assessments of my dataset to find any quality or tidiness issues that may interfere with my analysis.</p><p>In the end, I came up with the following issues to be cleaned:</p><ul><li>There were 30 null values in the price column.</li><li>There were 2 null values in the title column.</li><li>One of the entries was a pair of sunglasses, and not a book. This needed to be dropped.</li><li>98 values in the author column were missing.</li><li>Sequel to the previous point, all but one of these missing values were in the no_of_reviews column.</li><li>There were 1 null value in the cover_type column.</li><li>There were erroneous namings: no_of_reviews (to be changed to average_rating) and ratings (to be changed to no_of_reviews).</li><li>Change the datatypes of no_of_reviews, ranks and year to int, object and ordered category respectively.</li></ul><h4><strong>Cleaning.</strong></h4><p>As I usually do, I made a copy of the dataset to prep it for cleaning and preprocessing for further analysis. This was aimed at preserving the original dataset. All I did to clean the dataset was to <em>painstakingly </em>tackle the issues as listed above. There sure was a lot of pain.</p><p>Anyway, I did this using the trusty <a href="https://medium.com/@neto_a/using-spotifys-spotipy-for-exploratory-data-analysis-84ecbbb24887">Define-Code-Test</a> system.</p><p>The final cleaned dataset was then saved as a .csv file, ready for exploratory data analysis.</p><h4>EXPLORATORY DATA ANALYSIS</h4><p>In order for me to arrive at a definitive conclusion regarding what factors guided the purchase of the most popular books, ranging from price point to cover type, with everything in between, I needed to outline certain questions.</p><p><strong>Questions for Analysis.</strong></p><ul><li>What were the most popular books overall?</li><li>What were the most popular authors overall?</li><li>What was the most popular genre overall?</li><li>What was the distribution of popularity by genre for each year?</li><li>What was the yearly average price of books?</li><li>What cover types appealed to readers?</li><li>What were the most reviewed books in general?</li><li>What were the most reviewed authors in general?</li><li>What was the most reviewed genre in general?</li><li>What were the most popular books by genre?</li><li>What were the most popular authors by genre?</li></ul><p><em>Let us now discuss the insights.</em></p><p>The first thing I wanted to know was what the bestsellers overall were. The horizontal bar chart below shows that of all 431 unique books reviewed, the most popular book overall was <strong>StrengthsFinder 2.0</strong>, which appeared <strong>11</strong> times. This means that the book made the bestsellers’ list <em>every single year</em>. That is no small feat.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/940/1*4ysR3jmCqs9WQanjeRM7sA.png" /></figure><p>Following this, I sought to determine which author had the most entries in the list. I saw that Jeff Kinney was the author with the most entries across all the bestsellers’ lists, with <strong>14</strong>. This was closely followed by <strong>Gallup</strong> (11), <strong>The College Board</strong> (11) and <strong>Suzanne Collins</strong> (11).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/566/1*GKG_BD3BA30CMgJHYPDyrw.png" /></figure><p>There were two major genres: Fiction and Non-fiction. I then decided to find out which of the two was the more popular. It turned out that <strong>Non-fiction</strong> was the more popular genre, making up <strong>54.99% (237)</strong> of all books in the list, according to the donut chart shown below.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/410/1*z8vVHvihI-J9CTUAFLwTPA.png" /></figure><p>Sequel to the previous point, I decided to find out the spread of each genre’s popularity across all the years. It was then that Non-fiction was the more popular genre. The only place this trend did not follow was <strong>2009</strong>, where both genres were equally popular.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/651/1*haYzjAydiAq7rxyAR-dPMg.png" /></figure><p>I believe that customers have book cover preferences. To this end, I inferred from the horizontal bar chart below that the most popular cover type overall was <strong>Hardcover</strong>, which was sold in <strong>353</strong> books. This makes sense, as Hardcovers are generally more durable and protective than its closest type, Paperback.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/648/1*-GnbZGvP3bClJj--np_pIQ.png" /></figure><p>It is important to note that it’s one thing to be popular across the years, but it is another thing for customers to actively leave reviews. In this light, <strong>Where the Crawdads Sing</strong> was seen to be the most reviewed book <strong>(429,687 reviews),</strong> making its author, <strong>Delia Owens</strong>, the most reviewed author across all the years <strong>(1,289,061 reviews).</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*FCMUEGBi5Amqx-h6lPpH0A.png" /></figure><p>In line with the previous point, I thought to determine the genre that received more reviews. Although Non-fiction was the more popular genre by number of books in the overall list, <strong>Fiction (67.24%)</strong> was the more popular genre by average number of reviews.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/308/1*I5urUxpDgV7Eq9nq2t3DTA.png" /></figure><p>I also wanted to find out the average yearly trend in book pricing. The line chart shows that the average price of books in generally on a downward trend. In other words, books are generally becoming cheaper. <strong>2011</strong> had the highest average price compared to other years, though, which was nearly <strong>$18</strong>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/432/1*3WkGs2N0UhZeqjT3gfIKqA.png" /></figure><p><em>The following insights I generated, which were my last, can serve as guides for anyone looking for new books or authors, depending on their choice of genre.</em></p><p>Popular Books by Genre: In the <em>Non fiction</em> genre, the 3 most popular books were <strong>StrengthsFinder 2.0, The Official SAT Study Guide, and Publication Manual of the American Psychological Association</strong>. Meanwhile, in the <em>Fiction</em> genre, the 3 most popular books were <strong>Oh, the Places You’ll Go!, Fahrenheit 451, and The Book Thief</strong>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/633/1*xNQ3KJsT4NLRMJ31aM97sg.png" /></figure><p>Popular Authors by Genre: In the <em>Non fiction</em> genre, the 3 most popular authors were <strong>The College Board, Gallup, and Gary Chapman</strong>. Meanwhile, in the <em>Fiction</em> genre, the 3 most popular books were <strong>Jeff Kinney, Suzanne Collins, and Rick Riordan</strong>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/571/1*BEDrLzdsIo1Z5M4lYeNGRA.png" /></figure><p>An interactive dashboard was created using PowerBI, shown below.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*eN2_5EZGd0IAggxMJVT_BQ.jpeg" /><figcaption>The Dashboard for this Project</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/423/1*rc5hkUQIzDhEaqlqxESSjA.png" /><figcaption>Tooltip showing the Popular Cover Types for the Nonfiction Genre</figcaption></figure><h4>FINAL WORDS</h4><p>I hope with this thorough review, you will be guided on finding your next read. You will also be able to know where to find ample reviews to guide your choice. Since I’m a fiction lover, I think I’ll read <strong>Where the Crawdads Sing</strong> next. What about you?</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/346/1*vDWgPO9zMEzLI_2KFXPCaA.png" /><figcaption>Word Cloud Representation of the Dataset</figcaption></figure><p>As always, I am open to questions and corrections on this project and collaborations on future projects.</p><p>Till my next article, <em>¡adiós!</em></p><p>PS: Follow me on <a href="https://twitter.com/Naeto__">Twitter</a> and connect with me on <a href="https://www.linkedin.com/in/stephanie-netoafomachukwu-anyama-0b5257204">LinkedIn</a>. All code used and the link to the interactive report are stored in the Amazon Bestsellers (2009–2019) <a href="https://github.com/Neto-A/Amazon-Bestsellers-2009-2019-">repository</a> on my GitHub.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=8b6b05e73f12" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[The Role of Africa in Reducing World Hunger]]></title>
            <link>https://medium.com/@stephanie_a/the-role-of-africa-in-reducing-world-hunger-6f4459ef83a2?source=rss-8bc50dcf814a------2</link>
            <guid isPermaLink="false">https://medium.com/p/6f4459ef83a2</guid>
            <category><![CDATA[africa]]></category>
            <category><![CDATA[data-analytics]]></category>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[world-hunger]]></category>
            <category><![CDATA[python]]></category>
            <dc:creator><![CDATA[Stephanie Anyama]]></dc:creator>
            <pubDate>Tue, 18 Apr 2023 13:02:00 GMT</pubDate>
            <atom:updated>2023-04-18T13:50:15.508Z</atom:updated>
            <content:encoded><![CDATA[<h4>An Analysis of Africa’s Food Production and Supply Trends (2004–2013)</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/375/1*glgxYVnHGMGhjJl1tCURvA.png" /><figcaption>Africa as the Food Basket</figcaption></figure><h4>INTRODUCTION.</h4><p>Agriculture has been known to be the mainstay of the economies of African countries, mainly Sub-Saharan African countries. According to a <a href="https://www.ifpri.org/publication/agricultural-productivity-africa-trends-patterns-and-determinants">book</a> written on African agricultural productivity in 2016, agricultural productivity in Africa increased at a moderate rate between 1961 and 2012, although there are variations in the rate of growth in land, labour, and total factor productivities depending on country and region.</p><p>According to <a href="https://www.actionagainsthunger.org/the-hunger-crisis/world-hunger-facts/#:~:text=Global%20hunger%20crisis%20in%202023,and%20the%20COVID%2D19%20pandemic.">Action Against Hunger</a>, after steadily declining for a decade, world hunger is said to be on the rise, affecting nearly 10% of people globally. From 2019 to 2022, the number of undernourished people grew by as many as 150 million, a crisis driven largely by conflict, climate change, and the COVID-19 pandemic.</p><p>Therefore, in this project, I sought to find out for myself these trends in food production and supply, specifically between the years 2004 and 2013. I also aimed to describe how Africa can contribute to reducing world hunger. My process of achieving these aims will be covered in the following sections.</p><p><strong><em>Features of the Datasets.</em></strong></p><p>There were two datasets provided for this project. The first one covered the figures of food production, while the second covered food supply across 45 African countries.</p><p><strong>Food Production Dataset:</strong></p><p>1. Country: The name of the country.</p><p>2. Item: The name of the produce from each country.</p><p>3. Year: The year of production.</p><p>4. Value: The quantity of each item, measured in kilotonnes (kt).</p><p><strong>Food Supply Dataset:</strong></p><p>1. Country: The name of the country.</p><p>2. Year: The year of supply.</p><p>3. Value: The quantity supplied, measured in kilocalories per person per day (kcal/person/day).</p><h4>PRELIMINARY WRANGLING.</h4><p>For this project, I performed data wrangling and visualization using Python, and dashboard design using PowerBI. The process of wrangling the provided datasets was as follows: Gathering, Assessing, Cleaning.</p><p>The libraries used for this project were:</p><ul><li>Pandas and Numpy to read and manipulate the data as required.</li><li>Geopandas, Plotly and Folium to generate and design the choropleths. A choropleth is a filled map visualization. This was my first time using any of these libraries, so I sought assistance from <a href="https://plotly.com/python/choropleth-maps/">here</a>.</li><li>Matplotlib and Seaborn for all other visualizations.</li></ul><p>To gather, I read them into two different datasets, df1 for production and df2 for supply. From these two dataframes, I created more such as avg_vals, and years_dfto create the visualizations required.</p><pre>#importing all required libraries<br>import pandas as pd<br>import numpy as np<br>import matplotlib.pyplot as plt<br>import seaborn as sb<br>from matplotlib import patheffects<br>import folium<br>import geopandas as gpd<br>import os<br>import plotly.express as px<br><br>world = gpd.read_file(gpd.datasets.get_path(&#39;naturalearth_lowres&#39;))<br><br>%matplotlib inline</pre><p>After assessing the data, I decided to make a single change: to change the year column in both datasets to an ordered categorical column.</p><pre>#to change the years to an ordered categorical column in df1<br>ordinal_vars = {&#39;Year&#39;: [2004, 2005, 2006, 2007, 2008, 2009, 2010, 2011, 2012, 2013]}<br>for var in ordinal_vars:<br>    ordered_var = pd.api.types.CategoricalDtype(ordered = True,<br>                                                categories = ordinal_vars[var])<br>    df1[var] = df1[var].astype(ordered_var)<br><br>#to change the years to an ordered categorical column in df2<br>ordinal_var2 = {&#39;Year&#39;: [2004, 2005, 2006, 2007, 2008, 2009, 2010, 2011, 2012, 2013]}<br>for var in ordinal_var2:<br>    ordered_v = pd.api.types.CategoricalDtype(ordered = True,<br>                                                categories = ordinal_var2[var])<br>    df2[var] = df2[var].astype(ordered_v)</pre><p>Designing the choropleths was rather tricky, so I used the Plotly and Geopandas documentations, and this <a href="https://towardsdatascience.com/mapping-with-matplotlib-pandas-geopandas-and-basemap-in-python-d11b57ab5dac">Medium</a> article to guide me. Using the groupby function, I created two new dataframes containing the aggregate values of production and supply, by country.</p><p>Following that, I downloaded the shapefile for the map of Africa and read it into a geodataframe, map_df.</p><pre># set the filepath and load<br>fp = &#39;/Users/netoa/Downloads/afr_g2014_2013_0/afr_g2014_2013_0.shp&#39;<br>#reading the file stored in variable fp<br>map_df = gpd.read_file(fp)<br># check data type so we can see that this is not a normal dataframe, but a GEOdataframe<br>map_df.head()</pre><p>I then cleaned country names in both the geodataframe and my original dataframes. Afterwards, the index in map_df was set to the country names, and sorted alphabetically. I then joined map_df to my dataframes on the country name, thus creating new dataframes merged for production and merged2 for supply.</p><pre>#creating the merged and merged2 dataframes<br>map_df = map_df.sort_values(by=[&#39;ADM0_NAME&#39;], ascending=True)<br>map_df<br><br>merged = map_df.set_index(&#39;ADM0_NAME&#39;).join(prd.set_index(&#39;Country&#39;))<br><br>merged2 = map_df.set_index(&#39;ADM0_NAME&#39;).join(sup.set_index(&#39;Country&#39;))</pre><p>Now, I could design the choropleths.</p><pre>#designing the production choropleth<br>fig = px.choropleth(merged,<br>                   geojson=merged.geometry,<br>                   locations=merged.index,<br>                   color=&quot;Value&quot;,<br>                    labels={&quot;Value&quot;: &quot;Quantity Produced(kt)&quot;},<br>                title=&quot;Amount of Food Produced by Country&quot;,<br>                    color_continuous_scale=px.colors.sequential.Greens,<br>                   projection=&quot;mercator&quot;)<br>fig.update_geos(fitbounds=&quot;locations&quot;, visible=False)<br>fig.show()<br><br><br>#designing the supply choropleth<br>fig = px.choropleth(merged2,<br>                   geojson=merged2.geometry,<br>                   locations=merged2.index,<br>                   color=&quot;Value&quot;,<br>                    color_continuous_scale=px.colors.sequential.Greens,<br>                    labels={&quot;Value&quot;: &quot;Quantity Supplied(kcal/person/day)&quot;},<br>                title=&quot;Amount of Food Supplied by Country&quot;,<br>                   projection=&quot;mercator&quot;)<br>fig.update_geos(fitbounds=&quot;locations&quot;, visible=False)<br>fig.show()</pre><p>With those sorted, we can now go into the Exploratory Data Analysis part of this project.</p><h4>INSIGHTS AND DISCUSSION.</h4><p>In order for us to arrive at a definitive conclusion regarding the food production and supply trends in Africa from 2004 to 2013, certain questions needed to be asked of the data, as listed below.</p><p>1. What are the highest producing countries?</p><p>2. What countries supply the most food per capita?</p><p>3. What is the relationship between average food production and average food supply?</p><p>4. What were the yearly trends in total food production and supply?</p><p>5. What items were most produced?</p><p>6. What items were least produced?</p><p>7. Which countries produce and supply the most unique products?</p><p><strong><em>Now let us get into the insights.</em></strong></p><p>According to the map of Africa below, showing the spread of total food production, it is observed that <strong>Nigeria, Egypt, and South Africa</strong> produced the highest amounts of food with <strong>1,628,030kt, 877,498kt, and 579,592 kt</strong> respectively.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/775/1*PnMGoezXm8pBT2l-SPmOdg.png" /></figure><p>From the map showing the spread of the total food supplied per capita per day, it is clearly seen that <strong>Egypt, Algeria, and Morocco</strong> supplied the most food per capita with <strong>34,580, 33,055, and 32,967 kcal/person/day</strong> respectively. This is in spite of the fact that Algeria and Morocco did not produce the most food.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/653/1*15igBq7Muc38YtNLJhCMlg.png" /></figure><p>Furthermore, it is seen, according to the following scatterplot, that an increased average food production leads to an increased average per capita supply. I recommend a <em>more intentional</em> switch from subsistence farming, which is mostly practiced in Africa, to mechanized, commercial farming. This is aimed at increasing per capita supply of food, and upping the quantity that can be processed into other useful forms and exported to other countries.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/426/1*qZOKxCABowVC38jOucxc_g.png" /></figure><p>It is also important to note that there was a gradual growth in food production between 2005 and 2006, according to line chart on the left. However, production declined by 2007, increased again in 2008 and 2009 (although the difference wasn’t as substantial between the years. In spite of that, production has been steadily growing at a moderate pace since 2009. Median food production across all the years was 18kt.</p><p>Regarding food supply, there was a steady increase across all the years, with median supply being 2,376 kcal/person/day. This is despite the dip in production in 2007. It could be guessed that food production in 2006 was enough to support production and supply in 2007, although there must have been a slight population rise.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/940/1*YjyyR6gj6-GG_wZ0RObsHA.png" /></figure><p>I also sought to know what items were the most and least produced. The horizontal bar charts below make it clear that while <strong>Cassava and its products</strong> came up to <strong>1,118,948 metric kilotonnes</strong> across all 10 years, <strong>Aquatic Animals and Others</strong> only made up to <strong>13 metric kilotonnes</strong>, making it the least produced item.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*tlR3Q1w6745vXe0ZTyomkw.png" /></figure><p>It is, therefore, safe to recommend that more research can and should go into how cassava, sugarcane, and maize (top three items) can be processed into more useful finished products.</p><p><strong>Of all countries, Nigeria</strong> carried the day, producing <strong>54,000 metric kilotonnes of Cassava and Products</strong> in 2012, the highest produced by any country in any year.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/319/1*HQ7tIcTzVRtjOBjq5KiGow.png" /></figure><p>Finally, the last insight: There were 94 unique products in the production dataset. Of all 94, the <strong>United Republic of Tanzania</strong> produced 79, the most out of all the countries recorded. Surprisingly, the country was <em>not</em> in the top 5 most productive countries, by metric kilotonnes, coming in at <strong>33,280.2 kt</strong> on average with <strong>2188 kcal/person/day</strong> supplied on average. This is a pointer that the country’s agricultural sector may lean towards mere subsistence.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/316/1*kpR-ILAdlOVpKVf73G7wmQ.png" /></figure><p>The rest of the top 5 countries in this category were <strong>Morocco, South Africa, Kenya and Egypt</strong>. It is safe to assume that Morocco, which isn’t a high producer but a high supplier, and Kenya which is neither a high producer nor supplier, tend toward subsistence, like Tanzania. Egypt and South Africa, however, are definitely high exporting, commercial countries.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*KV-Al0USULs4qoSLN-cdUg.jpeg" /><figcaption>The Dashboard for this Project</figcaption></figure><blockquote>Interact with this dashboard <a href="https://app.powerbi.com/view?r=eyJrIjoiMWIyM2Y5YWEtNTczOC00MDYxLWJkYTYtZDc3ODkwMzhkMjcyIiwidCI6ImEzMjNmYmMzLTM3NzUtNDNhMi05MWYxLTA4YWY1ZTA1MTVhZSJ9">here</a>.</blockquote><h4>RECOMMENDATIONS.</h4><p>In conclusion, given all the insights obtained from these datasets, I recommend that:</p><p>· African countries need to be more intentional about a switch from subsistence to commercial agriculture. This can be done by increasing production, fostering research into new ways of growing crops or rearing animals, more ideas of food and livestock processing.</p><p>· Storage facilities should be developed and/or improved so ensure the longevity of produce.</p><p>· Pan-African trade measures should also be taken into consideration, as there are nations that may not have adequate arable land like others.</p><p>These steps, I’m sure, will increase per capita supply of food and the overall Gross Domestic Product of African countries. They will also ensure that Africa takes her place in the reduction, and hopefully, elimination of world hunger.</p><blockquote>Slides to this report can be viewed <a href="https://drive.google.com/file/d/1_ydXpYe2h5HJ5POTh48FQlD3PdS3BPgf/view?usp=share_link">here</a>.</blockquote><h4>FINAL WORDS.</h4><p>This project was particularly challenging, as I had to learn how to utilize new Python libraries and geographical shape files to create choropleths. I did enjoy the process, however.</p><p>As always, I am open to questions and corrections on this project and collaborations on future projects.</p><p>Till my next article, <em>¡adiós!</em></p><p>PS: Follow me on <a href="https://twitter.com/Naeto__">Twitter</a> and connect with me on <a href="https://www.linkedin.com/in/stephanie-netoafomachukwu-anyama-0b5257204">LinkedIn</a>. View my portfolio <a href="https://stephanieanyama.carrd.co/">here</a>. All code used and the link to the interactive report are stored in the Food Production in Africa <a href="https://github.com/Neto-A/Food-Production-in-Africa">repository</a> on my Github.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=6f4459ef83a2" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Long Time, No Rih: A Twitter Sentiment Analysis Of Rihanna’s Superbowl Halftime Performance]]></title>
            <link>https://medium.com/@stephanie_a/long-time-no-rih-a-twitter-sentiment-analysis-of-rihannas-superbowl-halftime-performance-cbbccf071f71?source=rss-8bc50dcf814a------2</link>
            <guid isPermaLink="false">https://medium.com/p/cbbccf071f71</guid>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[data-analytics]]></category>
            <category><![CDATA[sentiment-analysis]]></category>
            <dc:creator><![CDATA[Stephanie Anyama]]></dc:creator>
            <pubDate>Tue, 14 Mar 2023 13:12:10 GMT</pubDate>
            <atom:updated>2023-03-14T13:12:10.990Z</atom:updated>
            <content:encoded><![CDATA[<h4>Using Snscrape for Data Gathering…</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/445/1*2todvwL9BQs4JisiO2KKQw.png" /><figcaption>Rihanna at the SuperBowl</figcaption></figure><h4>INTRODUCTION.</h4><p>The day was Thursday 28th January 2016. Anti, the last studio album by Robyn Rihanna Fenty, was released.</p><p>Millions of fans around the world began expecting another album from her, but nothing was forthcoming. If anything, Rihanna blatantly evaded questions from journalists enquiring about the likelihood of an album. She always repeated, “a dot, a dot, and a dot”, exasperating fans and interviewers alike. Eventually, they (I included) began to beg for something, <em>anything. </em>But the hope for this elusive <em>anything </em>began to dwindle as the star started each of her multi-million-dollar businesses, Fenty Beauty, Savage x Fenty, Fenty Skin and Fenty. This hope essentially died when her son with fellow artiste, A$AP Rocky was born in May 2022.</p><p>Till <a href="https://twitter.com/rihanna/status/1574110540711923712?s=20"><em>this</em></a> happened, that is.</p><h3>Rihanna on Twitter: &quot;. pic.twitter.com/7oCnFsZH5U / Twitter&quot;</h3><p>pic.twitter.com/7oCnFsZH5U</p><p>Social media and news outlets were agog with the rare possibility of a <em>Rih-turn</em>, one on which they had anxiously been waiting and hoping. As a longtime fan of hers myself, I was buzzing with excitement. This performance came on the back of the release of <em>Lift Me Up </em>in October 2022, which Fenty sang for the <em>Black Panther: Wakanda Forever</em> soundtrack. Unfortunately, many fans were left disappointed by the track.</p><p>And so, on the 13th of February 2023, at halftime of the 57th Super Bowl game between Kansas City Chiefs and Philadelphia Eagles, Rihanna returned.</p><p>For this project, I decided to find out exactly how other fans around the world felt, what their opinions on the performance were, among other very interesting things.</p><p>As always, I employed the following workflow:</p><ul><li>Data Wrangling: Gathering, Assessing &amp; Cleaning</li><li>Data Preprocessing</li><li>Sentiment Analysis</li><li>Data Visualization &amp; Exploratory Data Analysis</li></ul><blockquote>Listen to the setlist <a href="https://music.apple.com/us/playlist/rihannas-super-bowl-lvii-halftime-show-set-list/pl.2d5e38b2cdaa4417932784ff19cc4fbe">here</a>, as you read along.</blockquote><h4>DATA WRANGLING.</h4><h4>Gathering.</h4><p>To gather the data, I scraped tweets using <strong>Snscrape</strong>. I opted against using Twitter’s official API, Tweepy as the tweets I needed were older than the limit set by Twitter (7 days). I installed and imported this library. For my sentiment analysis, I used the Natural Language Toolkit (<strong>nltk</strong>) library. Unlike Tweepy, I was not required to get any access keys with Snscrape.</p><p>To create the Word Cloud containing the most frequently used words, I installed and imported the <strong>wordcloud</strong> library. I also utilized the <strong>re</strong> and <strong>random</strong> libraries to extract certain words from the dataframe and create the function needed to generate the red colour for the word cloud, respectively.</p><pre>import snscrape.modules.twitter as sntwitter<br>import pandas as pd<br>import numpy as np<br>import re<br><br>import matplotlib.pyplot as plt<br>%matplotlib inline<br>import seaborn as sb<br><br>import textblob<br>from textblob import TextBlob<br><br>from wordcloud import WordCloud, ImageColorGenerator<br>from emot.emo_unicode import UNICODE_EMOJI # For emojis<br><br>import nltk<br>from nltk.corpus import stopwords # to get stopwords from NLTK library<br>from nltk.tokenize import word_tokenize # to create word tokens<br>from nltk.stem import WordNetLemmatizer # to reduce words to original form<br>from nltk.corpus import words # Get all words in english language<br><br>import string<br>import random</pre><p>Time for scraping, achieved using guidelines from <a href="https://www.freecodecamp.org/news/python-web-scraping-tutorial/#:~:text=Just%20remember%20that%20there%20is,7%20days%20old%20using%20Tweepy">this</a> article. I first created a list (data dictionary), tweets. Using the parameters described in the code block below, I specified that I was going to use snscrape’s twitter scraper, as the library is capable of scraping across various social media platforms. I set my tweet limit to 50,000 tweets and the timeframe at 13–15th February. For each tweet, I obtained the username, date created, number of likes, number of retweets, number of replies, tweet source and the actual tweet content.</p><p>All of this was then loaded into a dataframe, tweets_df, which was then saved as a csv file, tweets_rihanna.csv.</p><pre># Creating list to append tweet data to<br>tweets = []<br><br># Using TwitterSearchScraper to scrape data and append tweets to list<br>for i,tweet in enumerate(sntwitter.TwitterSearchScraper(&#39;#RihannaSuperBowl since:2023-02-13 until:2023-02-15&#39;).get_items()):<br>    if i&gt;50000:<br>        break<br>    tweets.append([tweet.user.username, tweet.date, tweet.likeCount, tweet.retweetCount, tweet.replyCount, tweet.lang,<br>                   tweet.sourceLabel, tweet.content])<br>    <br># Creating a dataframe to load the list<br>tweets_df = pd.DataFrame(tweets, columns=[&quot;user&quot;, &quot;date_created&quot;, &quot;number_of_likes&quot;, &quot;language&quot;, &quot;number_of_retweets&quot;, &quot;number_of_replies&quot;,<br>                                          &quot;source_of_tweet&quot;, &quot;tweet&quot;])</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/783/1*SD-a2IL377MO8BrNE34njw.png" /><figcaption>A section of the scraped dataset</figcaption></figure><p>After gathering my data, I decided what questions my project was going to answer. And they were:</p><ol><li>What are the tweets with the most engagements?</li><li>What is the overall sentiment of fans?</li><li>What tracks were most talked about by fans?</li><li>What were the most popular hashtags used by fans?</li><li>What were the most popular words used by fans?</li></ol><h4>Assessment.</h4><p>I carried out visual and programmatic assessments of my dataset to find any quality or tidiness issues that may interfere with my analysis. In total, I got 38,183 tweets. On assessment, I noted that a number of my tweets scraped were in English, Spanish and Portuguese, so I gathered them together using the query command and saved that as the definitive dataframe to be cleaned.</p><blockquote>It is important to note here that I tried translating the Spanish and Portuguese tweets using the googletrans library, but was unable to.</blockquote><p>The only other issues I observed, however, were:</p><ul><li>I observed that I’d made an error in naming the columns of the dataframe.</li><li>The date_created column needed to be changed from object to datetime.</li></ul><p><strong>Dataset Features.</strong></p><p>The final dataset contained 33,488 tweets to analyze, with these 8 distinct features.</p><ol><li>User: The handle of the Twitter user.</li><li>Date Created: The date the tweet was created and posted.</li><li>Number of Likes: The amount of likes the tweet received.</li><li>Number of Retweets: The amount of retweets the tweet received.</li><li>Number of Replies: The amount of replies the tweet received.</li><li>Source of Tweet: The kind of device used in creating the tweet i.e. Twitter for iPhone, Twitter for Android etc.</li><li>Tweet: The actual tweet content, with emojis.</li><li>Language: The machine-generated assumed language of the tweet.</li></ol><h4>Cleaning.</h4><p>As is my usual practice, I made a copy of the dataset for cleaning and preprocessing for further analysis. This was aimed at preserving the original dataset. All I did to clean the dataset was to tackle the issues as listed above. I did this using the trusty <a href="https://medium.com/@neto_a/using-spotifys-spotipy-for-exploratory-data-analysis-84ecbbb24887">Define-Code-Test</a> system.</p><p>The cleaned definitive dataset was then saved as a csv file, ready for preprocessing for sentiment analysis.</p><h4>DATA PREPROCESSING.</h4><p>Data preprocessing is a specialized type of data cleaning done in the preparation of tweets for sentiment analysis. Properly preprocessing data results in more accurate downstream processes. To do this, I created several functions which I applied to the ‘tweet’ column in my final_df dataframe to produce desired results.</p><p>These functions served several purposes, some of which were:</p><ol><li>To detect and extract hashtags in each tweet, and append to a new column.</li><li>To define and extract what track(s) had been mentioned in each tweet, so as to determine the most popular tracks.</li><li>To extract positive words used to describe the performance to a new column.</li></ol><p>Finally, after further extraction of stopwords and emojis, I created a new column with the processed tweets, ready for sentiment analysis.</p><h4>SENTIMENT ANALYSIS.</h4><p>Sentiment analysis is an approach to natural language processing (NLP) that identifies the emotional tone behind a body of text. It is also known as <a href="https://www.techtarget.com/searchbusinessanalytics/definition/opinion-mining-sentiment-mining#:~:text=Sentiment%20analysis%2C%20also%20referred%20to,product%2C%20service%2C%20or%20idea.">opinion mining</a>. Using TextBlob, I determined the polarity, and thus the sentiment expressed by each tweet. Polarity values range from -1 to +1. <strong>A polarity less than 0 is regarded as a <em>negative</em> sentiment, while a value greater than or equal to 0 is a <em>positive</em> sentiment.</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/615/1*dB3FXu1NfwR6xLVXej17vw.png" /><figcaption>A section through the processed dataset</figcaption></figure><p>Here, 92.6% of fans expressed positive sentiments towards Rihanna’s performance. It was described by many as ‘the best Super Bowl halftime performance ever’, ‘amazing’, while some others referred to her as ‘queen’ and ‘iconic’.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/309/1*iGR3iwMyJgScFNbEwSr-TQ.png" /><figcaption>Pie Chart Showing Fans’ Sentiments towards Rihanna’s Performance.</figcaption></figure><h4>DATA VISUALIZATION &amp; EXPLORATORY DATA ANALYSIS.</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/622/1*wE3eNY6yBln40yY6D-o__A.png" /><figcaption>Project Dashboard.</figcaption></figure><p>The dashboard for this project was created using Microsoft PowerBI. The insights obtained from exploring all 33,488 tweets are as follows:</p><ul><li>Engagements were high with 319,446 likes, 40,857 retweets, 11942 replies in total, spread over 27,289 unique users.</li><li><strong>92.6%</strong> of fans showed positive sentiments towards the performance.</li><li><strong>Diamonds</strong>, arguably one of the artiste’s greatest songs of all time, was the song most talked about, showing up in <strong>358 (21.96%)</strong> of all tweets where fans mentioned any tracks.</li><li>By far, the most talked about portion of the performance was the news of Rihanna’s pregnancy as it was mentioned <strong>3,249</strong> times.</li><li>Apart from the official <strong>#RihannaSuperBowl</strong> official hashtag used 9133 times (18.87%), fans also used <strong>#Rihanna</strong> 5361 times (11.08%).</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/584/1*U-f9xnzfhoTk9BEva7cf7A.jpeg" /><figcaption>Most Common Words Used in Tweets.</figcaption></figure><blockquote>Interact with this dashboard <a href="https://app.powerbi.com/view?r=eyJrIjoiOWE0ODAzMGQtYWE3MS00M2NmLWE5Y2QtYTAwMWEyNGUzNzZkIiwidCI6ImEzMjNmYmMzLTM3NzUtNDNhMi05MWYxLTA4YWY1ZTA1MTVhZSJ9">here</a>.</blockquote><h4>FINAL WORDS.</h4><p>Perhaps, the major thought running through the minds of many fans was, “Will we ever get an album?” For many, specifically the 467 fans who tweeted about an album, the answer was a clear “never”. This was in the light of her soaring businesses and her future with 2-under-2.</p><p><em>That hurts, Robyn.</em></p><p>Finally, while they were disappointed by the realization that an album was not forthcoming, fans expressed love and happiness for Rihanna, and praise for her tenacity to sing and engage in a choreographed dance on a <em>floating</em> <em>stage</em> while pregnant.</p><p>It is important to note that the initial announcement came late in September 2022. This 4+-month timeframe, in addition to Rihanna’s long hiatus, greatly influenced the success of this concert. I am certain that if Rihanna eventually decides to release an album, it will break streaming records.</p><p>As always, I am open to questions and corrections on this project and collaborations on future projects.</p><p>Till my next article, <em>¡adiós!</em></p><p>PS: Follow me on <a href="https://twitter.com/Naeto__">Twitter</a> and connect with me on <a href="https://www.linkedin.com/in/stephanie-netoafomachukwu-anyama-0b5257204">LinkedIn</a>. All code used and the link to the interactive report are stored in the Long Time, No Rih: A Twitter Sentiment Analysis <a href="https://github.com/Neto-A/Long-Time-No-Rih-A-Twitter-Sentiment-Analysis">repository</a> on my Github.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=cbbccf071f71" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Using Spotify’s Spotipy for Exploratory Data Analysis.]]></title>
            <link>https://medium.com/@stephanie_a/using-spotifys-spotipy-for-exploratory-data-analysis-84ecbbb24887?source=rss-8bc50dcf814a------2</link>
            <guid isPermaLink="false">https://medium.com/p/84ecbbb24887</guid>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[data-analytics]]></category>
            <category><![CDATA[spotify]]></category>
            <category><![CDATA[data-analysis]]></category>
            <dc:creator><![CDATA[Stephanie Anyama]]></dc:creator>
            <pubDate>Tue, 20 Dec 2022 18:44:03 GMT</pubDate>
            <atom:updated>2025-02-19T13:36:03.336Z</atom:updated>
            <content:encoded><![CDATA[<h4>An article describing how to use Spotify’s Web API, Spotipy for data analysis.</h4><h4><strong>INTRODUCTION.</strong></h4><p>About a month ago, after completing a <a href="https://github.com/Neto-A/Zentel-Analysis">project</a> on Zentel Network Service Center’s tickets, I stumbled upon an eye-catching <a href="https://medium.com/dev-genius/spotify-data-analysis-with-python-a727542beaa7">Medium</a> article. This article discussed using Spotify’s lightweight API, Spotipy for data wrangling and exploratory data analysis.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/676/1*ffOOut5qhD_SGGPARB3XqA.png" /></figure><p>Being a huge music fan, I was immediately intrigued by this API and decided to create my own project. I opted to work with a playlist, <em>Top Tracks of 2022- Nigeria, </em>curated by Spotify. It is a collection of the 50 songs that Nigerian consumers listened to the most, as the name implies.</p><p>I will be guiding you on the process and flow taken to complete this project, as follows:</p><ul><li>Data Wrangling: Gathering, Assessing and Cleaning</li><li>Data Visualization and Inferences</li></ul><p>Before we begin, come <em>ku lo sa</em> and listen to the playlist <a href="https://open.spotify.com/playlist/37i9dQZF1DX1W96TRrqJX4">here</a>. And here we go!</p><h4><strong>DATA WRANGLING.</strong></h4><p>In this project, I performed data wrangling using Python. In my previous <a href="https://medium.com/@neto_a/zentel-network-service-center-an-analysis-of-ticket-data-8802c955aeb2">article</a>, I described data wrangling as all the steps taken in gathering and transforming raw data to prepare it for analysis. The first step in every wrangling process is to actually <em>gather</em> the data.</p><p>To use Spotipy for gathering data, I needed to obtain client credentials: Client ID and Client Secret (both of which are kept hidden). To get these, follow these steps:</p><ul><li>Sign up for or sign into (as your case may be) the Spotify for Developers <a href="https://developer.spotify.com/">portal</a>. You can sign in normally if you already have a Spotify account.</li><li>Click on Dashboard.</li><li>Create an app for your project and write a brief description.</li><li>You will then be given your credentials.</li></ul><p>After getting them, I installed and imported all necessary libraries, the most important being Spotipy itself and SpotifyClientCredentials from spotify.oauth2. Using the required keys, I got authorized using the <a href="https://blog.devgenius.io/spotify-data-analysis-with-python-a727542beaa7">code</a> in the block below.</p><p>It is important to note that while trying to scrape the data, I kept running into connection time out issues. So I included a solution obtained from <a href="https://stackoverflow.com/questions/57994570/readtimeout-httpsconnectionpoolhost-port-443-read-timed-out-read-timeo">here</a> to rectify that.</p><p>Finally, I used two libraries that were new to me, Image from PIL and rembg to remove the background of one of the pictures I used in my PowerBI visualization.</p><pre>#importing libraries<br>import spotipy <br>from spotipy.oauth2 import SpotifyClientCredentials<br><br>#use client keys here<br>client_id = &#39;HIDDEN&#39;<br>client_secret = &#39;HIDDEN&#39;<br><br>client_credentials_manager = SpotifyClientCredentials(client_id= client_id, client_secret=client_secret)<br>sp = spotipy.Spotify(client_credentials_manager=client_credentials_manager)<br><br>import pandas as pd<br><br>import time<br><br>import  json<br><br>#connection timeout correction<br>import urllib3, socket<br>from urllib3.connection import HTTPConnection<br>    <br>HTTPConnection.default_socket_options = (HTTPConnection.default_socket_options + [<br>    (socket.SOL_SOCKET, socket.SO_SNDBUF, 1000000), #1MB in byte<br>            (socket.SOL_SOCKET, socket.SO_RCVBUF, 1000000)<br>        ])<br><br>#image background<br>from PIL import Image<br>import rembg</pre><p>Next, scrape the API to get the data. I created two functions: <em>getTrackID</em> to get the unique track IDs for each track in the playlist and <em>getTrackInfo</em> to get basic information about the tracks using their IDs. The information I needed were name, album, artist, release date, duration (which I converted from milliseconds to minutes) and track popularity.</p><pre># function to scrape track ids<br>def getTrackID(playlist_id):<br>    track_ids=[]<br>    play_list = sp.playlist(playlist_id)<br>    for item in play_list[&#39;tracks&#39;][&#39;items&#39;]:<br>        track=item[&#39;track&#39;]<br>        track_ids.append(track[&#39;id&#39;])<br>    return track_ids<br><br>track_ids = getTrackID(&#39;37i9dQZF1DX1W96TRrqJX4&#39;)<br><br># function to scrape track information<br>def getTrackInfo(track_id):<br>    meta = sp.track(track_id)<br>    track_details = {&#39;name&#39; : meta[&#39;name&#39;], &#39;album&#39;:meta[&#39;album&#39;][&#39;name&#39;], &#39;artist&#39;:meta[&#39;album&#39;][&#39;artists&#39;][0][&#39;name&#39;],<br>                &#39;release_date&#39;:meta[&#39;album&#39;][&#39;release_date&#39;], &#39;duration&#39;:round((meta[&#39;duration_ms&#39;]*0.001)/60.0, 2),<br>                    &#39;popularity&#39;:meta[&#39;popularity&#39;]}<br>    return track_details</pre><p>Afterwards, I appended this data into a dictionary, track_list, and read it into a dataframe, tracks_df.</p><pre>#create a data dictionary <br>track_list =[]<br>for i in range(len(track_ids)):<br>    time.sleep(.5)<br>    track = getTrackInfo(track_ids[i])<br>    track_list.append(track)<br><br>#create dataframe<br>tracks_df = pd.DataFrame(track_list, columns = [&#39;name&#39;, &#39;album&#39;, &#39;artist&#39;, &#39;release_date&#39;, &#39;duration&#39;, &#39;popularity&#39;])<br>tracks_df</pre><p>Then, I saved it into a csv file, tracks.csv, for easy access later. The dataframe looks like this:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/569/1*7lGLg4FtzrxNkbbyMjD9zg.png" /><figcaption>The track details dataset</figcaption></figure><p>Furthermore, I needed the audio features of the tracks. These features are numerical values defined and calculated by Spotify. They include danceability, energy, key etc. They each will be explained afterwards. To get these features, I used the track IDs scraped earlier. I created another data dictionary, track_features, to collect the audio features for each track. I then read these information into a dataframe, feat_df.</p><pre># to obtain track features<br>track_features = []<br>for id in track_ids:<br>        features = sp.audio_features(id)<br>        track_features.append(features)<br>feat_df = pd.DataFrame(columns = [&#39;danceability&#39;, &#39;energy&#39;, &#39;key&#39;, &#39;loudness&#39;, &#39;mode&#39;, <br>                                  &#39;speechiness&#39;, &#39;acousticness&#39;, &#39;instrumentalness&#39;, &#39;liveness&#39;, <br>                                  &#39;valence&#39;, &#39;tempo&#39;, &#39;type&#39;, &#39;id&#39;, &#39;uri&#39;, &#39;track_href&#39;, &#39;analysis_url&#39;, <br>                                  &#39;duration_ms&#39;, &#39;time_signature&#39;])<br>for item in track_features:<br>      for feat in item:<br>        feat_df = feat_df.append(feat, ignore_index=True)<br>feat_df.head()</pre><p>Finally, I saved this information into a csv file, track_features.csv, also for easy access later. The dataframe looks like this:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/749/1*9fdHXspt04NoBbQ29UuKXA.png" /><figcaption>The track features dataset</figcaption></figure><p>Now, I had to merge these dataframes together to create my actual dataset. This part was tricky because there were no common columns between them. So I created a new column in each of them, ‘one’. Using this, I was able to create the master dataset, full_tracklist, using inner merge while keeping both the right and left indexes as True.</p><pre>full_tracklist = pd.merge(tracks_df, feat_df, how=&#39;inner&#39;, left_index=True, right_index=True)<br>full_tracklist</pre><p>This merge, however, led to two columns, one_x and one_y, from each of the dataframes getting included in the dataset. I then dropped these columns and saved the dataframe as a csv file, track_list.csv. This dataset would be used for assessing and cleaning.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/861/1*J3fcN1djC5zJae-CbzNaNg.png" /><figcaption>A section of the master dataset</figcaption></figure><p>Before moving on to the second stage of wrangling, <em>assessing,</em> I posed these questions to be answered by my EDA:</p><ol><li>What are the most popular songs overall?</li><li>In what month were most of these songs released?</li><li>How did certain audio features influence popularity?</li></ol><p>This was important as it guided assessment of the data. It was pretty straightforward and I noted the following issues with my dataset:</p><ul><li>Datatype changes to be made: release_date to datetime and time_signature to string.</li><li>Extraneous columns: type, uri, track_href, analysis_uri, duration_ms to be dropped.</li></ul><p>Stage Three of wrangling, <em>cleaning</em>, was up next. As I like to do, I made a copy of the dataset. This copy was cleaned, to preserve the original dataset. All I did to clean the dataset was to tackle the issues as listed above. I did this using the Define-Code-Test system: <em>define</em> the problem, write the <em>code</em> to fix the problem, <em>test</em> your solution.</p><p>The cleaned dataset was saved into a csv file, track_cleaned.csv, ready for visualization.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/589/1*cVFNJ788p9_s_QibLc4QjA.png" /><figcaption>The cleaned column list (above) and a section of the dataset (below)</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/792/1*RDIXLXeMoAaIRWqx8X0mBA.png" /></figure><p>Wrangling is generally an iterative process, meaning that you can always go back to refine work done. Recall that I stated at the beginning of this article that I used two libraries to remove the background of a picture I wanted to use in my visualization: Image from PIL and rembg. This is a form of <em>gathering</em>.</p><p>I used this <a href="https://dev.to/0xkoji/only-7-lines-python-code-to-remove-background-with-rembg-4g74">code</a> to carry out the task:</p><pre>#import image<br>img = Image.open(&#39;/Users/netoa/Downloads/Asake_2.webp&#39;)<br>img<br><br>#remove background<br>img_bg_removed = rembg.remove(img)<br>img_bg_removed<br><br>#save image<br>img_bg_removed.save(&#39;/Users/netoa/Downloads/asake_final.png&#39;)</pre><p><em>Et voilà!</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/400/1*IKu1fCaXNUgSU9TfGaM7Sw.png" /><figcaption>Before</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/401/1*1QJW0houmFhSfP-mrTpUmg.png" /><figcaption>After</figcaption></figure><h4><strong>DATASET DESCRIPTION.</strong></h4><p>Now that I have the cleaned dataset down, I will describe some of the 19 features for easier understanding.</p><ol><li>Name: The name of the track.</li><li>Artist: The name of the performing artist.</li><li>Duration: The duration of the track in minutes, originally in milliseconds.</li><li>Popularity: The score assigned to each track based on its total streams, how recently and how frequently the song has been played.</li><li>Danceability: Danceability describes how suitable a track is for dancing based on a combination of musical elements including tempo, rhythm stability, beat strength, and overall regularity. A value of 0.0 is least danceable and 1.0 is most danceable.</li><li>Energy: Energy is a measure from 0.0 to 1.0 and represents a perceptual measure of intensity and activity. Typically, energetic tracks feel fast, loud, and noisy. For example, death metal has high energy, while a Bach prelude scores low on the scale. Perceptual features contributing to this attribute include dynamic range, perceived loudness, timbre, onset rate, and general entropy.</li><li>Speechiness: Speechiness detects the presence of spoken words in a track. The more exclusively speech-like the recording (e.g. talk show, audio book, poetry), the closer to 1.0 the attribute value.</li><li>Acousticness: A confidence measure from 0.0 to 1.0 of whether the track is acoustic. 1.0 represents high confidence the track is acoustic.</li><li>ID: The Spotify ID for the track.</li></ol><blockquote>Descriptions of most of the columns were obtained from Spotipy’s <a href="https://developer.spotify.com/documentation/web-api/reference/#/operations/get-audio-features">documentation</a>. The others can be found in the <a href="https://github.com/Neto-A/Spotify-Top-Tracks-NG-2022">repository</a> containing the project.</blockquote><h4><strong>DATA VISUALIZATION.</strong></h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/904/1*PdYHG7FKVB7lWjqsq1CXog.png" /><figcaption>The Overview page of my report.</figcaption></figure><p>I visualized the data using Microsoft PowerBI. The report is divided into three parts:</p><ul><li><strong>Overview:</strong> This part contains a general outlook of the playlist; the number of songs in the playlist, unique artistes, average length of the tracks (in minutes). It also includes the number of likes the playlist had received as at project completion, which refers to how many users added the playlist to their libraries. Finally, it contains the most popular songs by popularity scores, the artiste with the most songs in the playlist, the monthly trends in song release dates and the distribution of time signatures.</li><li><strong>Audio Features:</strong> This section purely describes how certain features influenced the popularity of the songs. It was going to be excessive to include all the features gathered. So, for the purpose of this project, I chose Duration, Acousticness, Speechiness, Energy, Danceability and Valence.</li><li><strong>Inferences: </strong>This simply contains the summary of all insights gained.</li></ul><blockquote>There are information buttons beside the charts to explain them, as the figures generally have no units.</blockquote><p>Here are some of the insights gained from the visualization:</p><ul><li>8 of the songs that made the list were released in September 2022, the highest of all the release months.</li><li>Songs with a speechiness of 0.15 (low speechiness) were the most preferred. This means that songs made up primarily of beats and instrumentals were greatly preferred.</li><li>Nigerians enjoyed songs with a midrange energy (0.65), i.e., moderately fast, loud and intense.</li><li>Songs with a mid-upper (0.6–0.75) valence were most preferred. Therefore, Nigerians enjoyed songs with some level of positivity to them.</li></ul><blockquote>All other inferences are listed in this complete interactive <a href="https://app.powerbi.com/view?r=eyJrIjoiMDAwMTBmNWItOWYwMy00NmE1LWFjMWYtOTdhNjY0NWM0MTUzIiwidCI6ImEzMjNmYmMzLTM3NzUtNDNhMi05MWYxLTA4YWY1ZTA1MTVhZSJ9">report</a>.</blockquote><h4>FINAL WORDS.</h4><p>Although this project was tricky and tasking, I thoroughly enjoyed working on it. I learned a lot, from using new libraries on Python to unlocking certain features on PowerBI. It gave me a decent insight into what exactly Nigerians enjoy and look out for in good music. I hope it helped you too, dear reader.</p><p>As always, I am open to questions and corrections on this project and collaborations on future projects.</p><p>Till my next article, <em>¡adiós!</em></p><p>PS: Follow me on <a href="https://twitter.com/Naeto__">Twitter</a> and connect with me on <a href="https://linkedin.com/in/stephanieanyama">LinkedIn</a>. All code used and the link to the interactive report are stored in the Spotify Top Tracks NG 2022 <a href="https://github.com/Neto-A/Spotify-Top-Tracks-NG-2022">repository</a> on my Github.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=84ecbbb24887" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Zentel Network Service Center: An Analysis of Ticket Data]]></title>
            <link>https://medium.com/@stephanie_a/zentel-network-service-center-an-analysis-of-ticket-data-8802c955aeb2?source=rss-8bc50dcf814a------2</link>
            <guid isPermaLink="false">https://medium.com/p/8802c955aeb2</guid>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[data-analytics]]></category>
            <category><![CDATA[hackathons]]></category>
            <category><![CDATA[data-visualization]]></category>
            <dc:creator><![CDATA[Stephanie Anyama]]></dc:creator>
            <pubDate>Thu, 17 Nov 2022 17:45:59 GMT</pubDate>
            <atom:updated>2025-02-19T13:32:58.121Z</atom:updated>
            <content:encoded><![CDATA[<p>In October, specifically 5th and 8th, I competed in Datafest Africa’s Data Hackathon. I participated as a member of a team, Analytical Minds.</p><p>The competition was a three-part contest. The first part involved making a machine learning model and performing exploratory data analysis. The second part, which was reserved for the top 25 teams from the first part, involved wrangling, modelling and visualizing a dataset using PowerBI only. The last part, for the top 5, was the presentation of the reports created.</p><p>As David Abu and the other organizers said, the aim of the hackathon was to enable each participant add a new project to their portfolio. With this in mind, I decided to do the visualization in the second stage of the contest on my own. And so, in this article, I will be guiding you through each step I took.</p><p>I carried out an analysis of Zentel Network Service Center’s ticket data, which ran between the 1st and 31st of December, 2020. The dataset contained 5,998 entries with 12 columns/features. Features ranged from Report ID, channel, operator, among others.</p><p>Data was wrangled using Python and Power Query, while visualization was done using PowerBI.</p><p><strong>METHODOLOGY</strong></p><p>Analysis of the data was done using the following steps:</p><ul><li>Data Wrangling: Gathering, Assessing and Cleaning</li><li>Data Modelling</li><li>Data Visualization</li></ul><p><strong>WHAT DID I WANT TO KNOW?</strong></p><p>Before diving into the process, I posed questions to be answered, most of which were provided in the brief received from the datathon organizers.</p><p>A. What was the general performance of the service center and its staff? In other words:</p><ol><li>How many tickets were opened?</li><li>From what 5 states were the most complaints received?</li><li>What channels were used most in lodging complaints?</li><li>What were the main faults?</li><li>Who were the quickest responding operators?</li><li>Who were the quickest resolving operators?</li></ol><p>B. The board wanted to keep ticket response time below 10 seconds, on average. What were the factors preventing this?</p><p>C. How good was staff performance? Who were the best and worst performing managers and operators?</p><p><strong>DATA WRANGLING.</strong></p><p>Wrangling of a dataset involves three main steps: gathering, assessing and cleaning. To <strong>gather</strong>, I downloaded the dataset using this <a href="https://github.com/PauloDaguvnor/DataFest-Datathon-Materials/blob/main/Zentel%20Network%20Service%20Ticket%20Data.xlsx">link</a> I had already been provided with. The Excel file obtained was at the time, partially modelled (discussed in the next section) with the fact and dimensions tables already prepared. Afterwards, I extracted the fact table into a separate .csv file. This was used for wrangling on Python.</p><p><strong>Assessing</strong> involves using visual (observation) and programmatic (using code) methods to extract the issues with the dataset. On assessment of the dataset, I noticed the dataset was relatively cleaner than most and wasn’t going to require a tedious cleaning process. However, I noted the following issues:</p><ul><li>Fault type had missing values.</li></ul><figure><img alt="A list of all the features of the dataset. The Fault Type column has 5472 entries, while all others have 5998." src="https://cdn-images-1.medium.com/max/288/1*Xj7sU-fMI_qquiOCkCyCQg.png" /></figure><ul><li>The column labels were written with capitalized titles, with no underscores. This is important because it was going to be impossible to use the labels for further work.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/621/1*sNN566cGerfWZ28jGWo7sw.png" /></figure><ul><li>The datatypes of ticket open time, ticket response time, issue response time, and ticket close time needed to be changed to the datetime datatype.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/288/1*Xj7sU-fMI_qquiOCkCyCQg.png" /></figure><p>To <strong>clean</strong> this data, I adjusted the labels, filled the missing values in Fault Type with ‘Unknown’ and changed the erroneous datatypes, in that order.</p><p>The cleaned data was then saved into another .csv file and appended to the master dataset.</p><p><strong>DATA MODELLING</strong></p><p>An <a href="https://powerbi.microsoft.com/en-us/what-is-data-modeling/#:~:text=Data%20modeling%20is%20the%20process,between%20those%20bits%20of%20data.">article</a> on Microsoft PowerBI’s site defines Data modelling as <strong>the process of analyzing and defining all the different data your business collects and produces, as well as the relationships between those bits of data</strong>. This part was extremely important to this dataset because it made analysis a lot easier, clearer and less error-prone.</p><p>Like I said in the previous section, the data was already partially modelled. The dimensions tables: Channel Type, Service Type, Employee, Fault Type and Location had already been defined in the master dataset.</p><p>I loaded the data into Power Query on PowerBI to transform it. On first attempt at modelling, all the connections had a Many-Many cardinality. To correct this, I had to get back into Power Query. These corrections were made:</p><ol><li>The column headers were renamed to the original format.</li><li>All irrelevant rows and columns, especially in the dimensions tables, were dropped.</li><li>A new column, Service Code, was created by duplicating Report ID and splitting the duplicate column by its right-most delimiter.</li><li>Sequel to the previous point, duplicate rows in the Service Type table were dropped.</li></ol><p>However, there were two key columns missing from the fact table: Response Duration (the amount of time taken to respond to a complaint) and Resolution Duration (the amount of time taken to fix any issues raised). Using the DAX formula, DATEDIFF, these columns were calculated from the dataset.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/870/1*BXeiKh6qUnyKqKCcdcLjhA.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/870/1*8vFh1hl_42GeQfFUIQ2vnw.png" /></figure><p>After this, the connections were made.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/858/1*5p7L7MHwxj43MRPJEv_2MQ.png" /><figcaption>The Completed Model</figcaption></figure><p>Finally, I was ready to move on to next stage: Data Visualization.</p><p><strong>DATA VISUALIZATION</strong></p><p>Visualization of this data was done using PowerBI. To reiterate, I listed out the questions I was to give solutions to in an earlier section of this article. To derive insights for cases such as the best and worst performing operators, I took standard deviation as my preferred measure of consistency against the usual average. This is because average values do not effectively compute the consistency of staff. Standard deviation, however, measures how far away from the overall average each person performed; literally, the <em>deviation</em> from the <em>mean.</em></p><p>Furthermore, to assess staff performance, I based my insights only on tickets in the Completed status. I thought, you wouldn’t track productivity with incomplete work, right?</p><p><strong>Insights: </strong>Some of them are as follows.</p><p><strong>A. General Performance:</strong></p><ol><li>Complaints received spiked between the 29th and 31st of the month.</li><li>Delta State recorded the most complaints, with 420 reports.</li><li>Social Media, Webform and USSD were the commonest channels used to lay complaints.</li></ol><p><strong>B. Ticket Response Time Analysis:</strong> Some of the main factors causing ticket response time to be greater than 10 seconds were as follows:</p><ol><li>Day of the Month: Given that most of the tickets were opened between the 29th and 31st, ticket response time was much slower.</li><li>Channel Used: Social Media complaints were attended to in more time than other channels.</li></ol><p><strong>C.</strong> <strong>Staff Performance:</strong></p><ol><li>Victor and his team completed 2485 tickets, the most of all the managers.</li><li>It was observed that Manager Wale consistently had the least response and resolution times.</li></ol><blockquote>All others can be found in the complete interactive dashboard, linked <a href="https://app.powerbi.com/view?r=eyJrIjoiYjE4ZDRjNTMtYzY4OS00ZjZmLTg2NmUtNTY2ZmJlMDcyYmVkIiwidCI6ImEzMjNmYmMzLTM3NzUtNDNhMi05MWYxLTA4YWY1ZTA1MTVhZSJ9&amp;pageName=ReportSection97bb0184f555c9ae58f6">here</a>.</blockquote><p><strong>RECOMMENDATIONS.</strong></p><ol><li>More operators should be employed, probably as contract staff, to handle the spike of complaints logged in between the 29th and 31st of the month.</li><li>Sequel to the first point, a manager like Victor may need more operators under him, as his team clearly receives the most complaints.</li><li>An automated system could be developed to handle late night complaints.</li><li>Best performing operators and managers should be recognized and rewarded adequately to improve the overall work ethic of staff.</li><li>Poorly performing operators should be probed into so as to find out the reasons for and ways to improve their current performance.</li></ol><p><strong>FINAL WORDS</strong></p><p>Even though I didn’t win this, I am grateful for the opportunity to have participated in Datafest’s maiden datathon. It was my first attempt at any hackathon and I am happy I did it. Going forward, I am open to collaborations on other projects and hackathons. Corrections on this project are welcome and appreciated.</p><p>Till my next article, <em>¡adiós!</em></p><p>PS: Follow me on <a href="https://twitter.com/Naeto__">Twitter</a> and connect with me on <a href="https://linkedin.com/in/stephanieanyama">LinkedIn</a>. All code used and the link to the interactive report are stored in the Zentel Analysis <a href="https://github.com/Neto-A/Zentel-Analysis">repository</a> on my Github.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=8802c955aeb2" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>