SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE [dbo].[bet] (
	[bookmaker_id] [int] NOT NULL,
	[provider_id] [int] NOT NULL,
	[round_id] [nvarchar](60) NOT NULL,
	[transaction_id] [nvarchar](60) NOT NULL,
	[game_id] [nvarchar](60) NOT NULL,
	[user_id] [nvarchar](60) NOT NULL,
	[bet_amount] [decimal](19, 2) NOT NULL,
	[balance] [decimal](19, 2) NOT NULL,
	[bet_timestamp] [datetime] NOT NULL,
	)
INSERT INTO bet 
([bookmaker_id],[provider_id],[round_id],[transaction_id],[game_id],[user_id],[bet_amount],[balance],[bet_timestamp])
VALUES
(1,1,'Round1','bet_tx1','game1','123',10.00,90.00,'2022-03-17 01:40:57.400'),
(1,1,'Round2','bet_tx2','game1','123',10.00,70.00,'2022-03-17 02:40:57.400'),
(1,1,'Round2','bet_tx3','game1','123',10.00,80.00,'2022-03-17 03:40:57.400'),
(1,1,'Round3','bet_tx4','game1','123',10.00,70.00,'2022-03-17 04:40:57.400')

CREATE TABLE [dbo].[win](
	[bookmaker_id] [int] NOT NULL,
	[provider_id] [int] NOT NULL,
	[round_id] [nvarchar](60) NOT NULL,
	[transaction_id] [nvarchar](60) NOT NULL,
	[user_id] [nvarchar](60) NOT NULL,
	[win_amount] [decimal](19, 2) NOT NULL,
	[balance] [decimal](19, 2) NOT NULL,
	[win_timestamp] [datetime] NOT NULL 
  ) 
INSERT INTO win 
([bookmaker_id],[provider_id],[round_id],[transaction_id],[user_id],[win_amount],[balance],[win_timestamp])
VALUES
(1,1,'Round1','win_tx1' ,'123',0.00,80.00,'2022-03-17 01:40:57.400'),
(1,1,'Round2','win_tx2' ,'123',10.00,80.00,'2022-03-17 02:40:57.400')

 
CREATE TABLE [dbo].[cancel](
	[bookmaker_id] [int] NOT NULL,
	[provider_id] [int] NOT NULL,
	[round_id] [nvarchar](60) NOT NULL,
	[transaction_id] [nvarchar](60) NOT NULL,
	[user_id] [nvarchar](60) NOT NULL,
	[cancel_timestamp] [datetime] NOT NULL 
 )  
 
 INSERT INTO cancel 
([bookmaker_id],[provider_id],[round_id],[transaction_id],[user_id],[cancel_timestamp])
VALUES
(1,1,'Round3','can_tx1' ,'123','2022-03-17 01:40:57.400')
7 rows affected

SELECT * from bet;
SELECT * from win;
SELECT * from cancel;


bookmaker_id
provider_id
round_id
transaction_id
game_id
user_id
bet_amount
balance
bet_timestamp


1
1
Round1
bet_tx1
game1
123
10.00
90.00
2022-03-17 01:40:57.400

1
1
Round2
bet_tx2
game1
123
10.00
70.00
2022-03-17 02:40:57.400

1
1
Round2
bet_tx3
game1
123
10.00
80.00
2022-03-17 03:40:57.400

1
1
Round3
bet_tx4
game1
123
10.00
70.00
2022-03-17 04:40:57.400




bookmaker_id
provider_id
round_id
transaction_id
user_id
win_amount
balance
win_timestamp


1
1
Round1
win_tx1
123
0.00
80.00
2022-03-17 01:40:57.400

1
1
Round2
win_tx2
123
10.00
80.00
2022-03-17 02:40:57.400




bookmaker_id
provider_id
round_id
transaction_id
user_id
cancel_timestamp


1
1
Round3
can_tx1
123
2022-03-17 01:40:57.400


/* get the round information for bet_tx1 
replace 'bet_tx1' with 'bet_tx2','bet_tx2' etc..
the STRING_AGG does not work in db fiddle but does work on my sql server
*/ 
select 
  bet.*, 
  win.*, 
  cancel.transaction_id as cancel_transaction_id, 
  cancel.cancel_timestamp 
from 
  (
    SELECT 
      round_id, 
      game_id, 
      provider_id, 
      bookmaker_id, 
      user_id, 
      STRING_AGG(bet.transaction_id, ', ') as bet_transaction_ids, 
      SUM(bet_amount) as total_bet, 
      MIN(bet_timestamp) as bet_timestamp, 
      opening_balance 
    FROM 
      (
        select 
          FIRST_VALUE(balance) OVER (
            PARTITION BY round_id 
            order by 
              bet_timestamp asc
          ) as opening_balance, 
          * 
        from 
          bet 
        where 
          bookmaker_id = 1 
          and provider_id = 1 
          and transaction_id = 'bet_tx1'
      ) as bet 
    group by 
      round_id, 
      game_id, 
      provider_id, 
      bookmaker_id, 
      user_id, 
      opening_balance
  ) as bet 
  LEFT JOIN (
    SELECT 
      round_id, 
      STRING_AGG(win.transaction_id, ', ') as win_transaction_ids, 
      SUM(win_amount) as total_won, 
      MAX(win_timestamp) as win_timestamp, 
      closing_balance 
    FROM 
      (
        select 
          FIRST_VALUE(balance) OVER (
            PARTITION BY round_id 
            order by 
              win_timestamp desc
          ) as closing_balance, 
          * 
        from 
          win
      ) as win 
    group by 
      win.round_id, 
      closing_balance
  ) as win ON bet.round_id = win.round_id 
  LEFT JOIN (
    SELECT 
      [round_id], 
      [transaction_id], 
      [cancel_timestamp] 
    FROM 
      cancel
  ) as cancel ON bet.round_id = cancel.round_id 
order by 
  total_won;


round_id
game_id
provider_id
bookmaker_id
user_id
bet_transaction_ids
total_bet
bet_timestamp
opening_balance
round_id
win_transaction_ids
total_won
win_timestamp
closing_balance
cancel_transaction_id
cancel_timestamp


Round1
game1
1
1
123
bet_tx1
10.00
2022-03-17 01:40:57.400
90.00
Round1
win_tx1
0.00
2022-03-17 01:40:57.400
80.00
null
null

bookmaker_id	provider_id	round_id	transaction_id	game_id	user_id	bet_amount	balance	bet_timestamp
1	1	Round1	bet_tx1	game1	123	10.00	90.00	2022-03-17 01:40:57.400
1	1	Round2	bet_tx2	game1	123	10.00	70.00	2022-03-17 02:40:57.400
1	1	Round2	bet_tx3	game1	123	10.00	80.00	2022-03-17 03:40:57.400
1	1	Round3	bet_tx4	game1	123	10.00	70.00	2022-03-17 04:40:57.400

bookmaker_id	provider_id	round_id	transaction_id	user_id	win_amount	balance	win_timestamp
1	1	Round1	win_tx1	123	0.00	80.00	2022-03-17 01:40:57.400
1	1	Round2	win_tx2	123	10.00	80.00	2022-03-17 02:40:57.400