Probo Very Interesting SQL Interview Question | Difficulty Level Hard | SQL For Data Analytics

Summary

In this video, the host tackles an intriguing problem from a Probo interview, involving a prediction app where users invest in poll options. The challenge is to redistribute winnings among users who correctly predicted the outcome of an event. The host explains the scenario using a SQL Server example, demonstrating how to calculate the proportion of winnings each winner should receive based on their investment. The solution involves joining tables, calculating the total amount to be distributed, and determining each winner's share proportionally. The video invites viewers to try the problem and share alternative solutions.

Takeaways

• 😀 The video is about solving an interesting problem that was asked in a Probo interview.
• 🧠 The problem involves understanding a betting scenario where users invest money on different poll options, and the money from incorrect options is redistributed to those who chose the correct one.
• 📊 The video explains the problem using a table structure with columns for user ID, poll ID, poll option ID, amount, and created at timestamp.
• 🏵️ An example scenario is provided where the Indian cricket team's match wins in 2022 are predicted, and the correct option's investors get the redistributed money.
• 💡 The video encourages viewers to try solving the problem on their own before revealing the solution.
• 💻 The solution is demonstrated using SQL Server, with a step-by-step explanation of the SQL queries involved.
• 📈 The video shows how to calculate the amount to be distributed to winners and the proportion of the investment made by each winner.
• 🔢 The SQL query includes joining tables, filtering losers, summing amounts, and using window functions to calculate proportions.
• 💼 The final SQL query multiplies the proportion by the amount to be distributed to determine the winning amount for each winner.
• 🌟 The video concludes by inviting viewers to share their thoughts or solutions in the comments and encourages likes and subscriptions.

Q & A

What is the main focus of the video?

The video explains how to solve an interesting SQL problem, which was asked during a Probo interview. The problem involves redistributing money among winners of a poll in a specific proportion based on their investment.

What app is referenced in the video, and what is its purpose?

The Probo app is referenced, and it allows users to answer predictive questions with four options. Users can win money if they correctly predict the outcome.

What is the example poll question mentioned in the video?

The example poll question is 'How many matches will the Indian cricket team win in 2022?' The user had to predict the number of wins in 2021 or mid-2022.

How is the winning option determined in the poll?

The winning option is determined based on the actual outcome of the event. For example, if India won 63 matches in 2022, the users who chose option C (which corresponds to 63 matches) are the winners.

How is the money redistributed among the winners?

The money from the losers (those who chose incorrect options) is redistributed to the winners proportionally based on the amount of money each winner invested in the correct option.

What is the SQL Server used for in the video?

The SQL Server is used to explain and execute the steps involved in calculating how the money from losing options is redistributed to winners, including how the amount each winner receives is determined.

What is the first step in solving the problem using SQL?

The first step is to filter out the users who chose the incorrect options (losers) and calculate the total money that needs to be redistributed.

How is the proportion of money to be distributed to each winner calculated?

The proportion is calculated by dividing the amount each winner invested by the total amount invested by all winners. For example, if a user invested 250 out of a total of 500, they would receive 50% of the redistributed money.

What SQL window function is used in the solution, and why?

The window function `SUM() OVER(PARTITION BY)` is used to calculate the total money invested by winners for each poll. This helps in determining the proportionate share of the winnings.

What is the final result of the SQL query in the example provided?

The final result of the SQL query shows the amount each winner receives, based on the proportion of their investment. For example, one user receives 750, another 150, and the third 600, proportionally from a total redistribution of 1500.