Running Total for Different Genders: Write an SQL query to find the total score for each gender at each day

This question was asked in Facebook interview, Ask is to provide Running Total for Different Genders.

Source: LeetCode

Difficulty: Medium

Company: Facebook

Running Total for Different Genders

Q. Write an SQL query to find the total score for each gender at each day. Order the result table by gender and day.

Solution

SELECT *, 
 SUM(score_points) over(Partition By gender Order by gender, day) as running_total
FROM PlayerScores;

Explanation

The solution for this is pretty straightforward. We will add another column to calculate the running total of scores.

This will be done using the SUM function with a WINDOW clause. We’ll partition the data by gender and order it by gender, day.

Essentially, we are using a window function here, as it allows us to group (partition) by gender and calculate the running total while maintaining the interval order.

Running Total for product

Ask is to calculate the running total for the amount column.

Solution

SELECT *, 
 SUM(amount) over(order by product_id rows between unbounded preceding and current row) as running_total
FROM products;

Explanation

Here by using rows between unbounded preceding and current row SQL is notified to do not consider the 2 value and same. Treat them individual and generate a running total.

I hope this explanation was helpful to you, considering sharing it with your friends, thank you.

Read more

• Mastering Pivot Table in Python: A Comprehensive Guide
• Data Science Interview Questions Section 3: SQL, Data Warehousing, and General Analytics Concepts
• Data Science Interview Questions Section 2: 25 Questions Designed To Deepen Your Understanding
• Data Science Questions Section 1: Data Visualization & BI Tools (Power BI, Tableau, etc.)
• Optum Interview Questions: 30 Multiple Choice Questions (MCQs) with Answers