Computing the YTD using the PARTITION BY clause-CodePudding

I've the following dataset:

I need to compute the YTD Revenue for each combination of sender_country, receiver_country. That is for example, I should get for Canada-Nigeria 5.08 for the year 2016 and 10.24 for 2017 (if i'm not wrong). I've tried to apply the following:

SELECT sender_country, receiver_country, date_move, revenue, SUM(revenue) OVER (PARTITION BY sender_country, receiver_country ORDER BY date_move) as YTD FROM (TABLE));

However, I'm getting the following result:

As you can see I'm aggregating the results for each pair of sender_country and receiver_country but I don't manage to "reset" the aggregated revenue for each year.

Thanks in advance

CodePudding user response：

Add the year to the partition with either TRUNC(date_move, 'YY') or EXTRACT(YEAR FROM date_move):

SELECT sender_country,
       receiver_country,
       date_move,
       revenue,
       SUM(revenue) OVER (
         PARTITION BY sender_country, receiver_country, TRUNC(date_move, 'YY')
         ORDER BY date_move
       ) AS YTD
FROM   table_name;