DAY 5: PROSQL45 CHALLENGE

A big thanks to Ankit Bansal the year-end challenge! I’m taking it on by solving 2 complex SQL questions every day from a playlist of 79 problems.

PROBLEM 9:

This problem is sourced from Leetcode.com : https://leetcode.ca/all/1159.html?

There are three tables: Users, Orders, and Items. The Users table contains user details, the Orders table records transactions, and the Items table has details of the items sold.

Write an SQL query to determine for each user whether the brand of the second item they sold (ordered by the sale date) matches their favorite brand. If a user sold fewer than two items, return "no" for that user.

Video Solution by Ankit: https://www.youtube.com/watch?v=1ias-sP_XAY&list=PLBTZqjSKn0IeKBQDjLmzisazhqQy4iGkb&index=9

?

Step-by-Step Solution Approach:

• I started by creating a ranked_orders CTE to rank each seller's orders to get the second sold item. I used the ROW_NUMBER() function, partitioned by o.seller_id, to order the sales by order_date for each seller. This allowed me to select the seller's ID, their favorite brand, the order date, the item's brand, and assign a rank to each order.
• Next, I checked if the second item sold (where order_rank = 2) matched the seller’s favorite brand. I used a CASE statement for this—if it matched, I returned "Yes", otherwise "No". To handle cases where there was no second item or the seller hadn’t sold anything (like user_id 1), I used the COALESCE function to default to "No". The LEFT JOIN ensured that all users were included, even those who hadn’t sold a second item.
• Lastly, I used the WHERE clause to filter the results, ensuring that only the second order was considered, or returned results for users who hadn’t made a second sale (using ro.order_rank IS NULL). This approach ensured that I checked each user’s second item and whether it matched their favorite brand.

CODE:

WITH ranked_orders
     AS (SELECT o.seller_id,
                o.order_date,
                i.item_brand,
                Row_number()
                  OVER (
                    partition BY o.seller_id
                    ORDER BY o.order_date) AS rn
         FROM   orders o
                LEFT JOIN items i
                       ON o.item_id = i.item_id)
SELECT u.user_id           AS seller_id,
       COALESCE(CASE WHEN u.favorite_brand = ro.item_brand 
	           THEN 'Yes' ELSE 'No' END, 'No') 
	          AS '2nd_item_fav_brand'
FROM   users u
       LEFT JOIN ranked_orders ro
              ON u.user_id = ro.seller_id
WHERE  ro.rn = 2
        OR ro.rn IS NULL ?        

PROBLEM 10:

Question: Given the tasks table with columns date_value and state, write an SQL query to find the start date, end date, and state of tasks. The start date should be the first date for each state group (e.g., the first occurrence of 'success' or 'fail'), and the end date should be the last date for each state group. The query should return the results grouped by the task's state, and for each state, the corresponding start and end dates.

Video Solution by Ankit: https://www.youtube.com/watch?v=WrToXXN7Jb4&list=PLBTZqjSKn0IeKBQDjLmzisazhqQy4iGkb&index=10

Step-by-Step Solution Approach:

• The first step is to group the data by state. I calculate a row number (rn) for each row within each state, ordered by date_value. Then, I subtract the row number from the date_value to create a group_date, which acts as a common identifier for consecutive date groups. This logic is placed in a CTE (Common Table Expression). The outcome of the CTE is as follows:

• The main query then selects the minimum (start_date) and maximum (end_date) date_value for each group of consecutive dates, grouped by state and group_date. Finally, the results are ordered by start_date, returning the consecutive date ranges for each state.

CODE:

With cte AS (
SELECT *,
ROW_NUMBER() OVER(PARTITION BY state ORDER BY date_value) AS rn,
DATEADD(DAY, -1 * ROW_NUMBER() OVER(PARTITION BY state ORDER BY date_value), date_value) AS group_date
FROM tasks
ORDER BY date_value)
SELECT MIN(date_value) AS start_date, MAX(date_value) AS end_date, state
FROM cte
GROUP BY state, group_date
ORDER BY start_date        

?

Let’s level up our SQL skills!

Join me on this journey as I tackle more challenges each day. Let’s improve our SQL skills together before the year ends!

?

Check out the full solutions on GitHub here!

?

#SQL #Database #SQLServer #MySQL #dataanalytics #SQLQuery #DataAnalysis #SQL #DataScience #DataAnalytics #RelationalDatabases #BusinessIntelligence #InterviewPreparation #sqlpractice #careerdevelopment #interviewquestions