<?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 Mansi Anand on Medium]]></title>
        <description><![CDATA[Stories by Mansi Anand on Medium]]></description>
        <link>https://medium.com/@mansiaanand?source=rss-9cf98c5ad0c8------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*8QE9hTth3Il7VFx74C_wvQ@2x.jpeg</url>
            <title>Stories by Mansi Anand on Medium</title>
            <link>https://medium.com/@mansiaanand?source=rss-9cf98c5ad0c8------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Wed, 18 Nov 2026 16:22:19 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@mansiaanand/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[SQL MAVEN MOVIES PROJECT]]></title>
            <link>https://mansiaanand.medium.com/sql-maven-movies-project-fea372683743?source=rss-9cf98c5ad0c8------2</link>
            <guid isPermaLink="false">https://medium.com/p/fea372683743</guid>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[database]]></category>
            <category><![CDATA[projects]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[data-analysis]]></category>
            <dc:creator><![CDATA[Mansi Anand]]></dc:creator>
            <pubDate>Tue, 02 May 2023 14:29:15 GMT</pubDate>
            <atom:updated>2023-05-02T14:29:15.049Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*4kLIlO8t9ybGlsuJfZ7zng.png" /></figure><p>Hi! This Project is a follow along project introduced by <strong>Maven Analytics</strong> which I have undergone in my MySQL Journey.</p><h4>It’s a good- follow along project if you’re at a Begineer Level or Intermediate Level.</h4><p><em>Disclaimer :Try to hop on to the Mid-Project Exercise Section first if you’re a beginner. <br>Feel free to hop onto Final-Project Exercise Section if are Experienced with the language.</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*lGgR_9EZezlpE3QtXleN4A.png" /><figcaption>ER DIAGRAM OF MAVEN MOVIES</figcaption></figure><p>ER DIAGRAM → ER diagrams are used to<strong> model data or information systems</strong> by showing the relationships between entities, attributes, and the data stored in those entities.</p><p>For learning more on ER diagrams — <a href="https://www.lucidchart.com/pages/er-diagrams">ER Diagram (ERD) — Definition &amp; Overview | Lucidchart</a></p><p>Don’t forget to check out <a href="https://www.mavenanalytics.io/">Maven Analytics</a></p><p><strong><em>Mid Project Exercises</em></strong></p><p>1. We will need a list of all staff members, including their first and last names, <br>email addresses, and the store identification number where they work.</p><pre>SELECT <br> first_name, <br> last_name, <br> email, <br> store_id<br>FROM staff;</pre><p>2. We will need separate counts of inventory items held at each of your two stores.</p><pre>SELECT <br> store_id, <br> COUNT(inventory_id) AS inventory_items<br>FROM inventory<br>GROUP BY <br> store_id;</pre><p>3. We will need a count of active customers for each of your stores. Separately, please.</p><pre>SELECT <br> store_id, <br> COUNT(customer_id) AS active_customers<br>FROM customer<br>WHERE active = 1<br>GROUP BY <br> store_id;</pre><p>4. In order to assess the liability of a data breach, we will need you to provide a count <br>of all customer email addresses stored in the database.</p><pre>SELECT <br> COUNT(email) AS emails<br>FROM customer;</pre><p>5. We are interested in how diverse your film offering is as a means of understanding how likely <br>you are to keep customers engaged in the future. Please provide a count of unique film titles <br>you have in inventory at each store and then provide a count of the unique categories of films you provide.</p><pre>SELECT <br> store_id, <br> COUNT(DISTINCT film_id) AS unique_films<br>FROM inventory<br>GROUP BY <br> store_id;</pre><pre>SELECT <br> COUNT(DISTINCT name) AS unique_categories<br>FROM category;</pre><p>6. We would like to understand the replacement cost of your films. <br>Please provide the replacement cost for the film that is least expensive to replace, <br>the most expensive to replace, and the average of all films you carry. ``</p><pre>SELECT <br> MIN(replacement_cost) AS least_expensive, <br> MAX(replacement_cost) AS most_expensive, <br> AVG(replacement_cost) AS average_replacement_cost<br>FROM film;</pre><p>7. We are interested in having you put payment monitoring systems and maximum payment <br>processing restrictions in place in order to minimize the future risk of fraud by your staff. <br>Please provide the average payment you process, as well as the maximum payment you have processed.</p><pre>SELECT<br> AVG(amount) AS average_payment, <br> MAX(amount) AS max_payment<br>FROM payment;</pre><p>8. We would like to better understand what your customer base looks like. <br>Please provide a list of all customer identification values, with a count of rentals <br>they have made all-time, with your highest volume customers at the top of the list.</p><pre>SELECT <br> customer_id, <br> COUNT(rental_id) AS number_of_rentals<br>FROM rental<br>GROUP BY <br> customer_id<br>ORDER BY <br> COUNT(rental_id) DESC;</pre><p><strong><em>Final Project Exercises </em></strong><br><em>1. My partner and I want to come by each of the stores in person and meet the managers. <br>Please send over the managers’ names at each store, with the full address <br>of each property (street address, district, city, and country please).</em></p><pre>SELECT <br> staff.first_name AS manager_first_name, <br> staff.last_name AS manager_last_name,<br> address.address, <br> address.district, <br> city.city, <br> country.country<br>FROM store<br> LEFT JOIN staff ON store.manager_staff_id = staff.staff_id<br> LEFT JOIN address ON store.address_id = address.address_id<br> LEFT JOIN city ON address.city_id = city.city_id<br> LEFT JOIN country ON city.country_id = country.country_id<br>;</pre><p><em>2. I would like to get a better understanding of all of the inventory that would come along with the business. <br>Please pull together a list of each inventory item you have stocked, including the store_id number, <br>the inventory_id, the name of the film, the film’s rating, its rental rate and replacement cost.</em></p><pre>SELECT <br> inventory.store_id, <br> inventory.inventory_id, <br> film.title, <br> film.rating, <br> film.rental_rate, <br> film.replacement_cost<br>FROM inventory<br> LEFT JOIN film<br> ON inventory.film_id = film.film_id<br>;</pre><p><em>3. From the same list of films you just pulled, please roll that data up and provide a summary level overview <br>of your inventory. We would like to know how many inventory items you have with each rating at each store.</em></p><pre>SELECT <br> inventory.store_id, <br> film.rating, <br> COUNT(inventory_id) AS inventory_items<br>FROM inventory<br> LEFT JOIN film<br> ON inventory.film_id = film.film_id<br>GROUP BY <br> inventory.store_id,<br> film.rating<br>;</pre><p><em>4. Similarly, we want to understand how diversified the inventory is in terms of replacement cost. We want to <br>see how big of a hit it would be if a certain category of film became unpopular at a certain store.<br>We would like to see the number of films, as well as the average replacement cost, and total replacement cost, <br>sliced by store and film category.</em></p><pre>SELECT <br> store_id, <br> category.name AS category, <br> COUNT(inventory.inventory_id) AS films, <br> AVG(film.replacement_cost) AS avg_replacement_cost, <br> SUM(film.replacement_cost) AS total_replacement_cost<br> <br>FROM inventory<br> LEFT JOIN film<br> ON inventory.film_id = film.film_id<br> LEFT JOIN film_category<br> ON film.film_id = film_category.film_id<br> LEFT JOIN category<br> ON category.category_id = film_category.category_id<br>GROUP BY <br> store_id, <br> category.name<br> <br>ORDER BY <br> SUM(film.replacement_cost) DESC<br>;</pre><p><em>5. We want to make sure you folks have a good handle on who your customers are. Please provide a list <br>of all customer names, which store they go to, whether or not they are currently active, <br>and their full addresses — street address, city, and country.</em></p><pre>SELECT <br> customer.first_name, <br> customer.last_name, <br> customer.store_id,<br> customer.active, <br> address.address, <br> city.city, <br> country.country<br>FROM customer<br> LEFT JOIN address ON customer.address_id = address.address_id<br> LEFT JOIN city ON address.city_id = city.city_id<br> LEFT JOIN country ON city.country_id = country.country_id<br>;</pre><p><em>6. We would like to understand how much your customers are spending with you, and also to know <br>who your most valuable customers are. Please pull together a list of customer names, their total <br>lifetime rentals, and the sum of all payments you have collected from them. It would be great to <br>see this ordered on total lifetime value, with the most valuable customers at the top of the list.</em></p><pre>SELECT <br> customer.first_name, <br> customer.last_name, <br> COUNT(rental.rental_id) AS total_rentals, <br> SUM(payment.amount) AS total_payment_amount<br>FROM customer<br> LEFT JOIN rental ON customer.customer_id = rental.customer_id<br> LEFT JOIN payment ON rental.rental_id = payment.rental_id<br>GROUP BY <br> customer.first_name,<br> customer.last_name<br>ORDER BY <br> SUM(payment.amount) DESC<br> ;</pre><p><em>7. My partner and I would like to get to know your board of advisors and any current investors.<br>Could you please provide a list of advisor and investor names in one table? <br>Could you please note whether they are an investor or an advisor, and for the investors, <br>it would be good to include which company they work with.</em></p><pre>SELECT<br> &#39;investor&#39; AS type, <br> first_name, <br> last_name, <br> company_name<br>FROM investor<br>UNION<br>SELECT <br> &#39;advisor&#39; AS type, <br> first_name, <br> last_name, <br> NULL<br>FROM advisor;</pre><p><em>8. We’re interested in how well you have covered the most-awarded actors. <br>Of all the actors with three types of awards, for what % of them do we carry a film?<br>And how about for actors with two types of awards? Same questions. <br>Finally, how about actors with just one award?</em></p><pre>SELECT<br> CASE <br> WHEN actor_award.awards = &#39;Emmy, Oscar, Tony &#39; THEN &#39;3 awards&#39;<br> WHEN actor_award.awards IN (&#39;Emmy, Oscar&#39;,&#39;Emmy, Tony&#39;, &#39;Oscar, Tony&#39;) THEN &#39;2 awards&#39;<br> ELSE &#39;1 award&#39;<br> END AS number_of_awards, <br> AVG(CASE WHEN actor_award.actor_id IS NULL THEN 0 ELSE 1 END) AS pct_w_one_film<br> <br>FROM actor_award<br>GROUP BY <br> CASE <br> WHEN actor_award.awards = &#39;Emmy, Oscar, Tony &#39; THEN &#39;3 awards&#39;<br> WHEN actor_award.awards IN (&#39;Emmy, Oscar&#39;,&#39;Emmy, Tony&#39;, &#39;Oscar, Tony&#39;) THEN &#39;2 awards&#39;<br> ELSE &#39;1 award&#39;<br> END</pre><p>If, you like this article — Please give a clap and encourage me to create more such project oriented blogs for you.</p><p>Feel free to reach out to me on <a href="https://www.linkedin.com/in/mansi-anand/">Mansi Anand | LinkedIn</a> ❤</p><p>Ciao, much love!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=fea372683743" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[1321. Restaurant Growth]]></title>
            <link>https://mansiaanand.medium.com/1321-restaurant-growth-69ae261480a0?source=rss-9cf98c5ad0c8------2</link>
            <guid isPermaLink="false">https://medium.com/p/69ae261480a0</guid>
            <category><![CDATA[leetcode]]></category>
            <category><![CDATA[mansianand]]></category>
            <category><![CDATA[solutions]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[data-analyst]]></category>
            <dc:creator><![CDATA[Mansi Anand]]></dc:creator>
            <pubDate>Thu, 02 Mar 2023 08:54:47 GMT</pubDate>
            <atom:updated>2023-03-02T08:54:47.142Z</atom:updated>
            <content:encoded><![CDATA[<h4>Day 14 SQL Leetcode Series -&gt; Solution + Explanation</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Qo7P0evFjF4tA4xpSgiJOw.png" /></figure><p>Table: Customer</p><pre>+---------------+---------+<br>| Column Name   | Type    |<br>+---------------+---------+<br>| customer_id   | int     |<br>| name          | varchar |<br>| visited_on    | date    |<br>| amount        | int     |<br>+---------------+---------+<br>(customer_id, visited_on) is the primary key for this table.<br>This table contains data about customer transactions in a restaurant.<br>visited_on is the date on which the customer with ID (customer_id) have visited the restaurant.<br>amount is the total paid by a customer.</pre><p>You are the restaurant owner and you want to analyze a possible expansion (there will be at least one customer every day).</p><p>Write an SQL query to compute moving average of how much customer paid in a 7 days window (current day + 6 days before) .</p><p>Return result table ordered by visited_on.</p><p>average_amount should be <strong>rounded to 2 decimal places</strong>, all dates are in the format (‘YYYY-MM-DD’).</p><p>The query result format is in the following example:</p><pre>Customer table:<br>+-------------+--------------+--------------+-------------+<br>| customer_id | name         | visited_on   | amount      |<br>+-------------+--------------+--------------+-------------+<br>| 1           | Jhon         | 2019-01-01   | 100         |<br>| 2           | Daniel       | 2019-01-02   | 110         |<br>| 3           | Jade         | 2019-01-03   | 120         |<br>| 4           | Khaled       | 2019-01-04   | 130         |<br>| 5           | Winston      | 2019-01-05   | 110         | <br>| 6           | Elvis        | 2019-01-06   | 140         | <br>| 7           | Anna         | 2019-01-07   | 150         |<br>| 8           | Maria        | 2019-01-08   | 80          |<br>| 9           | Jaze         | 2019-01-09   | 110         | <br>| 1           | Jhon         | 2019-01-10   | 130         | <br>| 3           | Jade         | 2019-01-10   | 150         | <br>+-------------+--------------+--------------+-------------+</pre><pre>Result table:<br>+--------------+--------------+----------------+<br>| visited_on   | amount       | average_amount |<br>+--------------+--------------+----------------+<br>| 2019-01-07   | 860          | 122.86         |<br>| 2019-01-08   | 840          | 120            |<br>| 2019-01-09   | 840          | 120            |<br>| 2019-01-10   | 1000         | 142.86         |<br>+--------------+--------------+----------------+</pre><pre>1st moving average from 2019-01-01 to 2019-01-07 has an average_amount of (100 + 110 + 120 + 130 + 110 + 140 + 150)/7 = 122.86<br>2nd moving average from 2019-01-02 to 2019-01-08 has an average_amount of (110 + 120 + 130 + 110 + 140 + 150 + 80)/7 = 120<br>3rd moving average from 2019-01-03 to 2019-01-09 has an average_amount of (120 + 130 + 110 + 140 + 150 + 80 + 110)/7 = 120<br>4th moving average from 2019-01-04 to 2019-01-10 has an average_amount of (130 + 110 + 140 + 150</pre><p><strong>Solution:</strong></p><p><strong>Using self — join:</strong></p><pre>SELECT x.visited_on AS visited_on, SUM(y.day_sum) AS amount,<br>       ROUND(AVG(y.day_sum), 2) AS average_amount<br>FROM<br>  (SELECT visited_on, SUM(amount) AS day_sum FROM Customer GROUP BY visited_on ) x,<br>  (SELECT visited_on, SUM(amount) AS day_sum FROM Customer GROUP BY visited_on ) y<br>WHERE DATEDIFF(x.visited_on, y.visited_on) BETWEEN 0 AND 6<br>GROUP BY x.visited_on<br>HAVING COUNT(y.visited_on) = 7<br>order by x.visited_on;</pre><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=69ae261480a0" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[1322. Ads Performance]]></title>
            <link>https://mansiaanand.medium.com/1322-ads-performance-3eec822eae1f?source=rss-9cf98c5ad0c8------2</link>
            <guid isPermaLink="false">https://medium.com/p/3eec822eae1f</guid>
            <category><![CDATA[leetcode]]></category>
            <category><![CDATA[solutions]]></category>
            <category><![CDATA[data-analyst]]></category>
            <category><![CDATA[mansianand]]></category>
            <category><![CDATA[sql]]></category>
            <dc:creator><![CDATA[Mansi Anand]]></dc:creator>
            <pubDate>Thu, 02 Mar 2023 08:47:54 GMT</pubDate>
            <atom:updated>2023-03-02T08:47:54.159Z</atom:updated>
            <content:encoded><![CDATA[<h4>Day 14 SQL Leetcode Series -&gt; Solution + Explanation</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Qo7P0evFjF4tA4xpSgiJOw.png" /></figure><p>Table: Ads</p><pre>+---------------+---------+<br>| Column Name   | Type    |<br>+---------------+---------+<br>| ad_id         | int     |<br>| user_id       | int     |<br>| action        | enum    |<br>+---------------+---------+<br>(ad_id, user_id) is the primary key for this table.<br>Each row of this table contains the ID of an Ad, the ID of a user and the action taken by this user regarding this Ad.<br>The action column is an ENUM type of (&#39;Clicked&#39;, &#39;Viewed&#39;, &#39;Ignored&#39;).</pre><p>A company is running Ads and wants to calculate the performance of each Ad.</p><p>Performance of the Ad is measured using Click-Through Rate (CTR) where:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/829/0*kxhrn9NGe_fe1agT.png" /></figure><p>Write an SQL query to find the ctr of each Ad.</p><p><strong>Round</strong> ctr to 2 decimal points. <strong>Order</strong> the result table by ctr in descending order and by ad_id in ascending order in case of a tie.</p><p>The query result format is in the following example:</p><pre>Ads table:<br>+-------+---------+---------+<br>| ad_id | user_id | action  |<br>+-------+---------+---------+<br>| 1     | 1       | Clicked |<br>| 2     | 2       | Clicked |<br>| 3     | 3       | Viewed  |<br>| 5     | 5       | Ignored |<br>| 1     | 7       | Ignored |<br>| 2     | 7       | Viewed  |<br>| 3     | 5       | Clicked |<br>| 1     | 4       | Viewed  |<br>| 2     | 11      | Viewed  |<br>| 1     | 2       | Clicked |<br>+-------+---------+---------+<br>Result table:<br>+-------+-------+<br>| ad_id | ctr   |<br>+-------+-------+<br>| 1     | 66.67 |<br>| 3     | 50.00 |<br>| 2     | 33.33 |<br>| 5     | 0.00  |<br>+-------+-------+<br>for ad_id = 1, ctr = (2/(2+1)) * 100 = 66.67<br>for ad_id = 2, ctr = (1/(1+2)) * 100 = 33.33<br>for ad_id = 3, ctr = (1/(1+1)) * 100 = 50.00<br>for ad_id = 5, ctr = 0.00, Note that ad_id = 5 has no clicks or views.<br>Note that we don&#39;t care about Ignored Ads.<br>Result table is ordered by the ctr. in case of a tie we order them by ad_id</pre><p><strong>SOLUTION:</strong></p><p>There are two ways to solve this problem:</p><ol><li>Using ifnull</li></ol><pre>select distinct ad_id, ifnull(<br>   round(<br>sum(action = &#39;Clicked&#39;) / (sum(action = &#39;Clicked&#39;) + sum(action = &#39;Viewed&#39;)) * 100, 2<br>    ) , 0<br>    ) as ctr<br>    from Ads<br>    group by ad_id<br>    order by ctr desc, ad_id;</pre><p>2. Using if with subquery:</p><pre>select ad_id, round(if(clicks + views = 0, 0, clicks / (clicks + views) * 100),<br>2) as ctr<br>from (<br> select ad_id, sum(if(action=&#39;Clicked&#39;, 1, 0)) as clicks,<br>sum(if(action=&#39;Viewed&#39;, 1, 0)) as views<br> from Ads<br> group by ad_id<br>) as a<br>order by ctr desc, ad_id asc</pre><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=3eec822eae1f" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[1327. List the Products Ordered in a Period]]></title>
            <link>https://mansiaanand.medium.com/1327-list-the-products-ordered-in-a-period-d6d2eaa99aca?source=rss-9cf98c5ad0c8------2</link>
            <guid isPermaLink="false">https://medium.com/p/d6d2eaa99aca</guid>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[mansianand]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[sql]]></category>
            <dc:creator><![CDATA[Mansi Anand]]></dc:creator>
            <pubDate>Thu, 02 Mar 2023 08:40:58 GMT</pubDate>
            <atom:updated>2023-03-02T08:40:58.872Z</atom:updated>
            <content:encoded><![CDATA[<h4>Day 14 SQL Leetcode Series -&gt; Solution + Explanation</h4><figure><img alt="Mansi Anand" src="https://cdn-images-1.medium.com/max/1024/1*Qo7P0evFjF4tA4xpSgiJOw.png" /></figure><p>Table: Products</p><pre>+------------------+---------+<br>| Column Name      | Type    |<br>+------------------+---------+<br>| product_id       | int     |<br>| product_name     | varchar |<br>| product_category | varchar |<br>+------------------+---------+<br>product_id is the primary key for this table.<br>This table contains data about the company&#39;s products.</pre><p>Table: Orders</p><pre>+---------------+---------+<br>| Column Name   | Type    |<br>+---------------+---------+<br>| product_id    | int     |<br>| order_date    | date    |<br>| unit          | int     |<br>+---------------+---------+<br>There is no primary key for this table. It may have duplicate rows.<br>product_id is a foreign key to Products table.<br>unit is the number of products ordered in order_date.</pre><p>Write an SQL query to get the names of products with greater than or equal to 100 units ordered in February 2020 and their amount.</p><p>Return result table in any order.</p><p>The query result format is in the following example:</p><pre>Products table:<br>+-------------+-----------------------+------------------+<br>| product_id  | product_name          | product_category |<br>+-------------+-----------------------+------------------+<br>| 1           | Leetcode Solutions    | Book             |<br>| 2           | Jewels of Stringology | Book             |<br>| 3           | HP                    | Laptop           |<br>| 4           | Lenovo                | Laptop           |<br>| 5           | Leetcode Kit          | T-shirt          |<br>+-------------+-----------------------+------------------+</pre><pre>Orders table:<br>+--------------+--------------+----------+<br>| product_id   | order_date   | unit     |<br>+--------------+--------------+----------+<br>| 1            | 2020-02-05   | 60       |<br>| 1            | 2020-02-10   | 70       |<br>| 2            | 2020-01-18   | 30       |<br>| 2            | 2020-02-11   | 80       |<br>| 3            | 2020-02-17   | 2        |<br>| 3            | 2020-02-24   | 3        |<br>| 4            | 2020-03-01   | 20       |<br>| 4            | 2020-03-04   | 30       |<br>| 4            | 2020-03-04   | 60       |<br>| 5            | 2020-02-25   | 50       |<br>| 5            | 2020-02-27   | 50       |<br>| 5            | 2020-03-01   | 50       |<br>+--------------+--------------+----------+</pre><pre>Result table:<br>+--------------------+---------+<br>| product_name       | unit    |<br>+--------------------+---------+<br>| Leetcode Solutions | 130     |<br>| Leetcode Kit       | 100     |<br>+--------------------+---------+</pre><pre>Products with product_id = 1 is ordered in February a total of (60 + 70) = 130.<br>Products with product_id = 2 is ordered in February a total of 80.<br>Products with product_id = 3 is ordered in February a total of (2 + 3) = 5.<br>Products with product_id = 4 was not ordered in February 2020.<br>Products with product_id = 5 is ordered in February a total of (50 + 50) = 100.</pre><p><strong>Solution:</strong></p><p>Explanation →</p><ul><li>We will use the subquery where we are getting all the product_id, sum(unit) which are ordered in month of February 2020 where we are selecting only those product_id which are having sum of unit greater than equal to 100 from order table</li><li>then join the product table along with the Orders table on PRODUCT_ID.</li></ul><pre>select product_name, unit<br>    from Products<br>    join (select product_id, sum(unit) as unit from Orders<br>         where order_date between &#39;2020-02-01&#39; and &#39;2020-02-29&#39;<br>         group by product_id<br>         having sum(unit) &gt;= 100) as orders_sum<br>    on Products.product_id = orders_sum.product_id;</pre><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=d6d2eaa99aca" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[1077. Project Employees III]]></title>
            <link>https://mansiaanand.medium.com/1077-project-employees-iii-1d5b01202036?source=rss-9cf98c5ad0c8------2</link>
            <guid isPermaLink="false">https://medium.com/p/1d5b01202036</guid>
            <category><![CDATA[leetcode]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[mansianand]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[database]]></category>
            <dc:creator><![CDATA[Mansi Anand]]></dc:creator>
            <pubDate>Tue, 21 Feb 2023 21:41:42 GMT</pubDate>
            <atom:updated>2023-02-21T21:41:42.955Z</atom:updated>
            <content:encoded><![CDATA[<h4>Day 13 SQL Leetcode Series -&gt; Solution + Explanation</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/819/1*K42lKX_SNSMKz7NdNnbd-w.png" /></figure><p>Table: Project</p><pre>+-------------+---------+<br>| Column Name | Type    |<br>+-------------+---------+<br>| project_id  | int     |<br>| employee_id | int     |<br>+-------------+---------+<br>(project_id, employee_id) is the primary key of this table.<br>employee_id is a foreign key to Employee table.<br>Each row of this table indicates that the employee with employee_id is working on the project with project_id.</pre><p>Table: Employee</p><pre>+------------------+---------+<br>| Column Name      | Type    |<br>+------------------+---------+<br>| employee_id      | int     |<br>| name             | varchar |<br>| experience_years | int     |<br>+------------------+---------+<br>employee_id is the primary key of this table.<br>Each row of this table contains information about one employee.</pre><p>Write an SQL query that reports the most experienced employees in each project. In case of a tie, report all employees with the maximum number of experience years.</p><p>Return the result table in any order.</p><p>The query result format is in the following example.</p><p>Example 1:</p><pre>Input: <br>Project table:<br>+-------------+-------------+<br>| project_id  | employee_id |<br>+-------------+-------------+<br>| 1           | 1           |<br>| 1           | 2           |<br>| 1           | 3           |<br>| 2           | 1           |<br>| 2           | 4           |<br>+-------------+-------------+<br>Employee table:<br>+-------------+--------+------------------+<br>| employee_id | name   | experience_years |<br>+-------------+--------+------------------+<br>| 1           | Khaled | 3                |<br>| 2           | Ali    | 2                |<br>| 3           | John   | 3                |<br>| 4           | Doe    | 2                |<br>+-------------+--------+------------------+<br>Output: <br>+-------------+---------------+<br>| project_id  | employee_id   |<br>+-------------+---------------+<br>| 1           | 1             |<br>| 1           | 3             |<br>| 2           | 1             |<br>+-------------+---------------+<br>Explanation: Both employees with id 1 and 3 have the most experience among the employees of the first project. For the second proj</pre><h4>Solution:</h4><p>Approach:</p><ol><li>Using IN Clause</li><li>Using DenseRank() with PartitionBy()</li></ol><p><strong><em>APPROACH 1</em></strong> →<em> Using the IN Clause:</em></p><pre>select project_id, employee_id<br>from Project<br>join Employee<br>using (employee_id)<br>where (project_id, experience_years) in (<br>    select project_id, max(experience_years)<br>    from Project<br>    join Employee<br>    using (employee_id)<br>    group by project_id)</pre><p><strong><em>APPROACH 2→ </em></strong><em>Using DenseRank() with PartitionBy()</em></p><pre>SELECT<br>    project_id,<br>    employee_id<br>FROM (<br>    SELECT<br>        p.project_id,<br>        p.employee_id,<br>        DENSE_RANK() OVER(PARTITION BY p.project_id ORDER BY e.experience_years DESC) <br>        as maxexp<br>    FROM project as p JOIN employee as e<br>    ON p.employee_id = e.employee_id<br>    ) <br>WHERE maxexp = 1</pre><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=1d5b01202036" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[1076. Project Employees II]]></title>
            <link>https://mansiaanand.medium.com/1076-project-employees-ii-9dd92a852303?source=rss-9cf98c5ad0c8------2</link>
            <guid isPermaLink="false">https://medium.com/p/9dd92a852303</guid>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[mansianand]]></category>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[leetcode]]></category>
            <category><![CDATA[database]]></category>
            <dc:creator><![CDATA[Mansi Anand]]></dc:creator>
            <pubDate>Tue, 21 Feb 2023 21:27:18 GMT</pubDate>
            <atom:updated>2023-02-21T21:43:23.366Z</atom:updated>
            <content:encoded><![CDATA[<h4>Day 13 SQL Leetcode Series -&gt; Solution + Explanation</h4><figure><img alt="Mansianand" src="https://cdn-images-1.medium.com/max/819/1*K42lKX_SNSMKz7NdNnbd-w.png" /></figure><p>Table: Project</p><pre>+-------------+---------+<br>| Column Name | Type    |<br>+-------------+---------+<br>| project_id  | int     |<br>| employee_id | int     |<br>+-------------+---------+<br>(project_id, employee_id) is the primary key of this table.<br>employee_id is a foreign key to Employee table.</pre><p>Table: Employee</p><pre>+------------------+---------+<br>| Column Name      | Type    |<br>+------------------+---------+<br>| employee_id      | int     |<br>| name             | varchar |<br>| experience_years | int     |<br>+------------------+---------+<br>employee_id is the primary key of this table.</pre><p>Write an SQL query that reports all the <strong>projects</strong> that have the most employees.</p><p>The query result format is in the following example:</p><pre>Project table:<br>+-------------+-------------+<br>| project_id  | employee_id |<br>+-------------+-------------+<br>| 1           | 1           |<br>| 1           | 2           |<br>| 1           | 3           |<br>| 2           | 1           |<br>| 2           | 4           |<br>+-------------+-------------+</pre><pre>Employee table:<br>+-------------+--------+------------------+<br>| employee_id | name   | experience_years |<br>+-------------+--------+------------------+<br>| 1           | Khaled | 3                |<br>| 2           | Ali    | 2                |<br>| 3           | John   | 1                |<br>| 4           | Doe    | 2                |<br>+-------------+--------+------------------+</pre><pre>Result table:<br>+-------------+<br>| project_id  |<br>+-------------+<br>| 1           |<br>+-------------+<br>The first project has 3 employees while the second one has 2.</pre><h3>Solution</h3><p>Approach:</p><p>Step 1: Find the maximum occurrence of project_id in table Project</p><ul><li>you can use either the max() or count operator.</li></ul><p>Step 2: Now, select project_id with the maximum occurrence from table Project</p><pre>select project_id<br>from Project<br>group by project_id<br>having count(employee_id) &gt;= (<br> select count(employee_id) as cnt<br> from Project<br> group by project_id<br> order by cnt desc<br> limit 1<br>);</pre><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=9dd92a852303" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[1075. Project Employees I]]></title>
            <link>https://mansiaanand.medium.com/1075-project-employees-i-bac858a36ada?source=rss-9cf98c5ad0c8------2</link>
            <guid isPermaLink="false">https://medium.com/p/bac858a36ada</guid>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[13days]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[mansianand]]></category>
            <category><![CDATA[leetcode]]></category>
            <dc:creator><![CDATA[Mansi Anand]]></dc:creator>
            <pubDate>Tue, 21 Feb 2023 21:20:51 GMT</pubDate>
            <atom:updated>2023-02-21T21:20:51.498Z</atom:updated>
            <content:encoded><![CDATA[<h4>Day 13 SQL Leetcode Series -&gt; Solution + Explanation</h4><figure><img alt="Mansianand" src="https://cdn-images-1.medium.com/max/819/1*K42lKX_SNSMKz7NdNnbd-w.png" /></figure><p>Table: Project</p><pre>+-------------+---------+<br>| Column Name | Type    |<br>+-------------+---------+<br>| project_id  | int     |<br>| employee_id | int     |<br>+-------------+---------+<br>(project_id, employee_id) is the primary key of this table.<br>employee_id is a foreign key to Employee table.</pre><p>Table: Employee</p><pre>+------------------+---------+<br>| Column Name      | Type    |<br>+------------------+---------+<br>| employee_id      | int     |<br>| name             | varchar |<br>| experience_years | int     |<br>+------------------+---------+<br>employee_id is the primary key of this table.</pre><p>Write an SQL query that reports the average experience years of all the employees for each project, rounded to 2 digits.</p><p>The query result format is in the following example:</p><pre>Project table:<br>+-------------+-------------+<br>| project_id  | employee_id |<br>+-------------+-------------+<br>| 1           | 1           |<br>| 1           | 2           |<br>| 1           | 3           |<br>| 2           | 1           |<br>| 2           | 4           |<br>+-------------+-------------+</pre><pre>Employee table:<br>+-------------+--------+------------------+<br>| employee_id | name   | experience_years |<br>+-------------+--------+------------------+<br>| 1           | Khaled | 3                |<br>| 2           | Ali    | 2                |<br>| 3           | John   | 1                |<br>| 4           | Doe    | 2                |<br>+-------------+--------+------------------+</pre><pre>Result table:<br>+-------------+---------------+<br>| project_id  | average_years |<br>+-------------+---------------+<br>| 1           | 2.00          |<br>| 2           | 2.50          |<br>+-------------+---------------+<br>The average experience years for the first project is (3 + 2 + 1) / 3 = 2.00 and for the second project is (3 + 2) / 2 =</pre><h4>Solution:</h4><pre>select project_id, round(avg(experience_years), 2) as average_years<br>from Project as p<br>left join Employee as e<br>on p.employee_id = e.employee_id<br>group by project_id;</pre><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=bac858a36ada" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[1112. Highest Grade For Each Student]]></title>
            <link>https://mansiaanand.medium.com/1112-highest-grade-for-each-student-b46d66debe8d?source=rss-9cf98c5ad0c8------2</link>
            <guid isPermaLink="false">https://medium.com/p/b46d66debe8d</guid>
            <category><![CDATA[leetcode]]></category>
            <category><![CDATA[mansianand]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[sql]]></category>
            <dc:creator><![CDATA[Mansi Anand]]></dc:creator>
            <pubDate>Tue, 07 Feb 2023 10:15:10 GMT</pubDate>
            <atom:updated>2023-02-07T10:16:40.293Z</atom:updated>
            <content:encoded><![CDATA[<h4>Day 12 SQL -&gt; Solution + Explanation</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*6EDFI2xXSeWwPtk3S7dZqg.png" /></figure><p>Table: Enrollments</p><pre>+---------------+---------+<br>| Column Name   | Type    |<br>+---------------+---------+<br>| student_id    | int     |<br>| course_id     | int     |<br>| grade         | int     |<br>+---------------+---------+<br>(student_id, course_id) is the primary key of this table.</pre><p>Write a SQL query to find the highest grade with its corresponding course for each student. In case of a tie, you should find the course with the smallest course_id. The output must be sorted by increasing student_id.</p><p>The query result format is in the following example:</p><pre>Enrollments table:<br>+------------+-------------------+<br>| student_id | course_id | grade |<br>+------------+-----------+-------+<br>| 2          | 2         | 95    |<br>| 2          | 3         | 95    |<br>| 1          | 1         | 90    |<br>| 1          | 2         | 99    |<br>| 3          | 1         | 80    |<br>| 3          | 2         | 75    |<br>| 3          | 3         | 82    |<br>+------------+-----------+-------+</pre><pre>Result table:<br>+------------+-------------------+<br>| student_id | course_id | grade |<br>+------------+-----------+-------+<br>| 1          | 2         | 99    |<br>| 2          | 2         | 95    |<br>| 3          | 3         | 82    |<br>+------------+-----------+-------+</pre><h3>Solution</h3><p>Using Subquery with IN operator:</p><pre>select student_id, min(course_id) as course_id,grade<br>from Enrollments<br>where (student_id,grade) in <br>    (select student_id,max(grade) from Enrollments group by student_id)<br>group by student_id, grade</pre><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=b46d66debe8d" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[1107. New Users Daily Count Problem]]></title>
            <link>https://mansiaanand.medium.com/1107-new-users-daily-count-problem-373b753eaf2f?source=rss-9cf98c5ad0c8------2</link>
            <guid isPermaLink="false">https://medium.com/p/373b753eaf2f</guid>
            <category><![CDATA[mansianand]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[leetcode]]></category>
            <category><![CDATA[sql]]></category>
            <dc:creator><![CDATA[Mansi Anand]]></dc:creator>
            <pubDate>Tue, 07 Feb 2023 10:13:20 GMT</pubDate>
            <atom:updated>2023-02-07T10:13:20.318Z</atom:updated>
            <content:encoded><![CDATA[<h4>Day 12 SQL -&gt; Solution + Explanation</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*6EDFI2xXSeWwPtk3S7dZqg.png" /></figure><p>Table: Traffic</p><pre>+---------------+---------+<br>| Column Name   | Type    |<br>+---------------+---------+<br>| user_id       | int     |<br>| activity      | enum    |<br>| activity_date | date    |<br>+---------------+---------+<br>There is no primary key for this table, it may have duplicate rows.<br>The activity column is an ENUM type of (&#39;login&#39;, &#39;logout&#39;, &#39;jobs&#39;, &#39;groups&#39;, &#39;homepage&#39;).</pre><p>Write an SQL query that reports for every date within at most 90 days from today, the number of users that logged in for the first time on that date. Assume today is 2019–06–30.</p><p>The query result format is in the following example</p><pre>Traffic table:<br>+---------+----------+---------------+<br>| user_id | activity | activity_date |<br>+---------+----------+---------------+<br>| 1       | login    | 2019-05-01    |<br>| 1       | homepage | 2019-05-01    |<br>| 1       | logout   | 2019-05-01    |<br>| 2       | login    | 2019-06-21    |<br>| 2       | logout   | 2019-06-21    |<br>| 3       | login    | 2019-01-01    |<br>| 3       | jobs     | 2019-01-01    |<br>| 3       | logout   | 2019-01-01    |<br>| 4       | login    | 2019-06-21    |<br>| 4       | groups   | 2019-06-21    |<br>| 4       | logout   | 2019-06-21    |<br>| 5       | login    | 2019-03-01    |<br>| 5       | logout   | 2019-03-01    |<br>| 5       | login    | 2019-06-21    |<br>| 5       | logout   | 2019-06-21    |<br>+---------+----------+---------------+</pre><pre>Result table:<br>+------------+-------------+<br>| login_date | user_count  |<br>+------------+-------------+<br>| 2019-05-01 | 1           |<br>| 2019-06-21 | 2           |<br>+------------+-------------+<br>Note that we only care about dates with non zero user count.<br>The user with id 5 first logged in on 2019-03-01 so he&#39;s not counted on 2019</pre><h3>Solution:</h3><p>USING SUBQUERY:</p><pre>select mindate as login_date, count(user_id) as user_count<br>from (select user_id, min(activity_date) as mindate<br>        from traffic <br>        where activity = &#39;login&#39;<br>        group by user_id) t<br>where datediff(&#39;2019-06-30&#39;, mindate) &lt;= 90<br>group by mindate;</pre><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=373b753eaf2f" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[1126. Active Businesses]]></title>
            <link>https://mansiaanand.medium.com/1126-active-businesses-f23e429d0bd4?source=rss-9cf98c5ad0c8------2</link>
            <guid isPermaLink="false">https://medium.com/p/f23e429d0bd4</guid>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[mansianand]]></category>
            <category><![CDATA[leetcode]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[data-analyst]]></category>
            <dc:creator><![CDATA[Mansi Anand]]></dc:creator>
            <pubDate>Tue, 07 Feb 2023 09:52:25 GMT</pubDate>
            <atom:updated>2023-02-07T09:52:25.643Z</atom:updated>
            <content:encoded><![CDATA[<h4>Day 12 SQL -&gt; Solution + Explanation</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*6EDFI2xXSeWwPtk3S7dZqg.png" /></figure><p>Table: Events</p><pre>+---------------+---------+<br>| Column Name   | Type    |<br>+---------------+---------+<br>| business_id   | int     |<br>| event_type    | varchar |<br>| occurences    | int     | <br>+---------------+---------+<br>(business_id, event_type) is the primary key of this table.<br>Each row in the table logs the info that an event of some type occured at some business for a number of times.</pre><p>Write an SQL query to find all active businesses.</p><p>An active business is a business that has more than one event type with occurrences greater than the average occurrences of that event type among all businesses.</p><p>The query result format is in the following example:</p><pre>Events table:<br>+-------------+------------+------------+<br>| business_id | event_type | occurences |<br>+-------------+------------+------------+<br>| 1           | reviews    | 7          |<br>| 3           | reviews    | 3          |<br>| 1           | ads        | 11         |<br>| 2           | ads        | 7          |<br>| 3           | ads        | 6          |<br>| 1           | page views | 3          |<br>| 2           | page views | 12         |<br>+-------------+------------+------------+</pre><pre>Result table:<br>+-------------+<br>| business_id |<br>+-------------+<br>| 1           |<br>+-------------+ <br>Average for &#39;reviews&#39;, &#39;ads&#39; and &#39;page views&#39; are (7+3)/2=5, (11+7+6)/3=8, (3+12)/2=7.5 respectively.<br>Business with id 1 has 7 &#39;reviews&#39; events (more than 5) and 11 &#39;ads&#39; events (more than 8) so</pre><h4>SOLUTION:</h4><p>METHOD 1- Using Self Join</p><ol><li>First, take the average of ‘occurences’ group by ‘event_type’.</li><li>Second, join Events table on ‘event_type’.</li><li>Third, the ‘occurences’ should be greater than the average of ‘occurences’</li><li>Finally, select ‘business_id’ from events table.</li></ol><pre>select business_id                                      # 4<br>(select event_type, avg(occurences) as avg_occurences   # 1<br> from events as e1<br> group by event_type<br>) as temp1<br>join events as e2 on temp1.event_type = e2.event_type   # 2<br>where e2.occurences &gt; temp1.avg_occurences              # 3<br>having count(distinct temp1.event_type) &gt; 1             </pre><p>METHOD 2 -&gt; Using the OVER() clause followed by Partition by:</p><p><em>I like solving this problem using the over clause personally, because it make the code easily understandable and more effective.</em></p><p>We are making a subquery with over clause : —</p><p>1. Inside the subquery we are finding the average occurences avg(occurences), and then following by Over clause we are partitioning it by the event_type.</p><p>2. In the main outer query we are then comparing the occurences which are greater then the avg occurences.</p><p>3. Then, group it by business_id which are having the event_type which are atleast greater than 1.</p><pre>select business_id<br>from<br>(<br> select *, avg(occurences) over(PARTITION by event_type) e_avg from Events<br>) t <br>where occurences &gt; e_avg<br>GROUP BY business_id<br>HAVING count(event_type) &gt; 1</pre><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=f23e429d0bd4" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>