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

• Write a query to identify the employee(s) whose salary is closest to the average salary of the company.
• Display all months where sales exceeded the average monthly sales.
• Find the most common value (mode) in a specific column.
• Identify employees who have never received a performance review.
• Write a query to retrieve the first order placed by each customer.