SQL for Data Science Day 26 | Top 50 Leetcode Question Solving Day 4 | Leetcode | Bhuvnesh Bhatt

Описание: Get ready for Day 26 of our SQL for Data Science challenge! In this video, we're stepping up the complexity with three fantastic LeetCode problems that focus on advanced data manipulation techniques: counting non-matches with LEFT JOIN, using Self-Joins for temporal comparisons, and calculating averages across grouped process times.

Mastering these patterns is key to solving real-world data problems!

❓ Problems Covered:

1. 1581. Customer Who Visited but Did Not Make Any Transactions (LEFT JOIN and COUNT)
The Goal: Find customers who visited the mall but did not make a transaction, and count the number of such visits.

The Technique: We use a LEFT JOIN to connect the Visits table to the Transactions table. Crucially, we filter the results using the WHERE clause:
WHERE t.transaction_id IS NULL.
This isolates only the visits that had no matching transaction record.

The Aggregation: We use COUNT(*) and GROUP BY to tally the number of non-transactional visits per customer.

2. 197. Rising Temperature (Self-Join and Date Functions)
The Goal: Find the id of dates where the temperature was higher than the previous day.

The Technique: This is a classic Self-Join problem. We join the Weather table to itself (aliased as w1 for today and w2 for yesterday) on a condition that links a date to its prior date.
We use a Date Function like DATEADD or DATEDIFF, depending on the SQL dialect, to ensure w1.recordDate is exactly one day after w2.recordDate.

The Comparison: We apply the filter:
WHERE w1.temperature is greater than w2.temperature.

Key Concept: Using a Self-Join to compare rows within the same table based on a time gap.

3. 1661. Average Time of Process per Machine (Complex Self-Join and AVG)
The Goal: Calculate the average processing time for each machine, where process time is end timestamp minus start timestamp.

The Technique: We perform a more complex Self-Join on the Activity table, matching start records to their end records using both machine id and process id.

a_start.machine_id equals a_end.machine_id
a_start.process_id equals a_end.process_id

The Calculation: We compute the time difference (a_end.timestamp minus a_start.timestamp) and apply the AVG aggregate function.
Finally, we group by machine id and round the result to three decimal places.

💡 SQL Concepts Used:
• LEFT JOIN and Self-Join
• WHERE clause with IS NULL
• COUNT and AVG
• GROUP BY
• Date and Time Functions like DATEADD or DATEDIFF
• ROUND for formatting

🔔 Don't Miss Out!
Subscribe and turn on notifications for daily SQL LeetCode solutions!

Hit the Like button if you enjoyed tackling these advanced join techniques!

Tell us in the comments which of these three LeetCode patterns you found most useful!

#SQL #DataScience #LeetCode #SQLJoins #SelfJoin #GroupBy #SQLAggregation #DataAnalytics #CodingChallenge #bhuvneshbhatt #bhuvnesh #bhatt