Find Aggregated Data Between Two Dates in Two Tables Where One is Updated Weekly and Other is Updated Hourly

Question

I have data in two different tables, one is updated every week or once in the middle of the week if needed, and the other table is updated every hour or so because it has more data. The first table, can be seen as

agent_id | rank | ranking_date
---------------------------
     1   |  1   | 2022-03-21
     2   |  2   | 2022-03-21
     1   |  4   | 2022-03-14
     2   |  3   | 2022-03-14
     1   |  2   | 2022-03-10
   

And the second table contains detailed information on sales.

agent_id | call_id | talk_time | product_sold | amount | call_date
------------------------------------------------------------------
   1     |    1    |    13     |      1       |   53   |2022-03-10
   1     |    2    |    24     |      2       |    2   |2022-03-10
   2     |    3    |    43     |      4       |   11   |2022-03-10
   1     |    4    |    31     |      -       |    0   |2022-03-10
   2     |    5    |    12     |      -       |    0   |2022-03-10
   1     |    6    |    11     |      -       |    0   |2022-03-11
   1     |    7    |    35     |      2       |   79   |2022-03-11
   2     |    8    |    76     |      -       |    0   |2022-03-11
   1     |    9    |    42     |      1       |   23   |2022-03-11
   2     |   10    |    69     |      -       |    0   |2022-03-11

How can I merge the two tables to get their aggregated information? Remember the ranks change at the beginning of every week, and the sales happen every day. But the rankings can also be changed in the middle of the week if needed. So what I am trying to get is created an aggregated table for understanding the sales by each agent. Something like this

agent_id | rank | ranking_date | total_calls_handled | total_talktime | total_amount
------------------------------------------------------------------------------------
   1     |  1   | 2022-03-21   |        100          |      875       |     3000 (this is 3/21 - today)
   2     |  2   | 2022-03-21   |        120          |      576       |     3689 (this is 3/21 - today)
   1     |  4   | 2022-03-14   |        210          |      246       |     1846 (this is 3/14 - 3/21)
   2     |  3   | 2022-03-14   |        169          |      693       |     8562 (this is 3/14 - 3/21)
   1     |  2   | 2022-03-10   |        201          |      559       |     1749 (this is 3/7 - 3/10)

So the data is aggregated for each agent from 7-10, 10 - 14, then 14-21. Also, if say, the latest ranking date is 2022-03-21, and today is 2022-03-23, the query returns aggregation until today.

[Edit]: added table and data details Table and data details: Rankings table:

• agent_id: unique_id of the agent
• rank: rank of an agent assigned updated every Monday or if needed
• ranking_date: date when agent's ranking was last updated (Automatically every Monday or if needed)

Sales Table:

• agent_id: unique_id of the agent
• call_id: unique_id for a call
• talk_time: duration of the call
• product_sold: unique_id of the product sold (- if agent was unsuccessful to sell)
• amount: commission earned by the agent (therefore same product_id has different amount) (0 if agent was unsuccessful to sell)
• call_date: date when which call was made

[Edit 2]: Here is SQLFiddle.

Answer 1

Here we join where ranking_date and call_date are in the same week. If you make calls sunday you will need to check whether it falls in the same week as you want.
The syntax in the query is for SQL server, as the SQL Fiddle given. You will need to modify the line of the join to

on date_part(w,r.ranking_date) = date_part(w,s.call_date)

which should be compatible with Google Redshift.

select 
  r.agent_id,
  r.rank,
  r.ranking_date,
  count(s.call_id) TotalCalls,
  sum(s.talk_time) TotalTime,
  sum(s.amount) TotalAmount
from rankings r
left join sales s
on datename(ww,r.ranking_date)= datename(ww,s.call_date)
group by
  r.agent_id,
  r.rank,
  r.ranking_date
GO

agent_id | rank | ranking_date | TotalCalls | TotalTime | TotalAmount
-------: | ---: | :----------- | ---------: | --------: | ----------:
       1 |    1 | 2022-03-21   |          0 |      null |        null
       1 |    2 | 2022-03-10   |         10 |       356 |         168
       1 |    4 | 2022-03-14   |          0 |      null |        null
       2 |    2 | 2022-03-21   |          0 |      null |        null
       2 |    3 | 2022-03-14   |          0 |      null |        null

db<>fiddle here