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

• Day 13 of Learning Python for Data Science: Mastering Pivot, Apply and RegEx
• Practice day 12 of Learning Python for Data Science
• Day 12 of Learning Python for Data Science – Pandas
• Day 10 Of Learning Python for Data Science – NumPy Array In Python
• Day 9 of Learning Python for Data Science – Queries Related To Functions In Python