Populate empty values from another table

Question

Let us say that I have two SQL tables

Employee Recognition Table

Employee Id	Reward Date	Coupon
1	1/1/2020	null
1	1/2/2020	null
1	1/3/2020	null
2	2/1/2020	null
2	2/2/2020	null
3	2/2/2020	null

Coupons

Employee Id	Coupon
1	COUPON1
1	COUPON2
1	COUPON3
2	COUPON4

What I want to do is allot coupons to all the employee uniquely, example

• employee 1 has three coupons so they should be allotted
• employee 2 just has 1 coupon so 1 should get allotted
• employee 3 has none

So the output should be something like

Employee Recognition Table Updated

Employee Id	Reward Date	Coupon
1	1/1/2020	COUPON1
1	1/2/2020	COUPON2
1	1/3/2020	COUPON3
2	2/1/2020	COUPON4
2	2/2/2020	null
3	2/2/2020	null

Also the table contains a lot of records both tables above 100k records so wondering what a performant query can look like. I have thought about using lateral joins but the speed seems to be the issue there.

Answer 1

Use below

select * except(pos)
from (
  select Employee_Id, Reward_Date, 
    row_number() over(partition by Employee_Id order by Reward_Date) pos
  from recognitions
)
left join (
  select Employee_Id, Coupon, 
    row_number() over(partition by Employee_Id order by Coupon) pos
  from coupons
)
using (Employee_Id, pos)
-- order by Employee_Id, Reward_Date    

if applied to sample data in your question - output is