Another question one of my students has sent me. This question tests your understanding of SQL Group Functions . Given the following betting activity table (bet_tbl table) :
As you can see, each row represents one placed bet, and includes the following fields:
- betID – bet number
- CustomerID – customer number
- betDate – the date in which this bet was made
- betOutcome – the bet outcome, 0 represents a loss and 1 represents a win.
- amount – the amount of money the customer has lost or won.
First task – Create a query to display the biggest loss, and the biggest win for each customer:
Second Task – Create a query to display the overall amount of win / loss for each customer:
CREATE TABLE bet_tbl
(betID int,
customerID int,
betDate date,
betOutcome bit,
amount int)
INSERT INTO bet_tbl
VALUES (1,1,getdate(),0,500 )
INSERT INTO bet_tbl
VALUES (2,1,getdate(),0,200 )
INSERT INTO bet_tbl
VALUES (3,1,getdate(),1,1500 )
INSERT INTO bet_tbl
VALUES (4,1,getdate(),0,300 )
INSERT INTO bet_tbl
VALUES (5,2,getdate(),0,350)
INSERT INTO bet_tbl
VALUES (6,2,getdate(),1,200 )
INSERT INTO bet_tbl
VALUES (7,2,getdate(),1,20 )
INSERT INTO bet_tbl
VALUES (8,2,getdate(),0,600)
SELECT customerID , MAX(CASE WHEN betOutcome=0 THEN amount ELSE 0 END) AS 'Highest loss', MAX(CASE WHEN betOutcome=1 THEN amount ELSE 0 END) AS 'Highest profit' FROM bet_tbl GROUP BY customerID SELECT customerID , SUM(CASE WHEN betOutcome=0 THEN amount*-1 ELSE amount END) AS 'Total' FROM bet_tbl GROUP BY customerID