project1_quetion4.sql

/*
  How much money each staff has processed to date?
*/

SELECT DISTINCT subquery.staff_id, subquery.staff,
       SUM(subquery.payment_amount) OVER(PARTITION BY subquery.staff) AS total_amt_received
FROM (
      SELECT staff.staff_id staff_id,
             CONCAT(staff.first_name, ' ', staff.last_name) AS staff,
             payment.amount payment_amount, payment.payment_date payment_date
      FROM staff
      JOIN payment
      ON staff.staff_id = payment.staff_id
) subquery
ORDER BY total_amt_received DESC;