Put Condition to CASE expression (SQL)-CodePudding

Is there anyway that I can filter the customers whose total bets on in-play football is more than 50% of their total bets everywhere? This is what I have in mind.

There are 2 tables, where the sportsbook_bets contains all bets from all customers (a customer may place multiple bets).

Sample Data:

Customer table

customer_id
1
2
...

sportsbook_bet table

customer_id	sport_name
1	football
1	football
1	hockey
1	basketball
2	tennis
2	football
2	hockey
2	basketball
...	...

Based on the tables above, the query should return customer 1 since the total bets placed on football is at least 50% of the total bet everywhere; where customer 2 only have 25% (thus doesn't get queried).

SELECT  c.customer_id
FROM    customers c INNER JOIN sportsbook_bets s 
ON c.customer_id=s.customer_id
WHERE 50 < ROUND((SUM(CASE WHEN s.sport_name = 'football' AND s.in_play_yn='Y' THEN 1 ELSE 0 END)/COUNT(s.bet_id))*100,0);

CodePudding user response：

I'm going to assume that you need some other columns from your customer table, otherwise you don't need that table to get customer_id, as that column is already in sportsbook_bets table.

One solution is to use a subqueries:

select distinct 
            C.customer_id
From customers C
Join sportsbook_bets S On S.customer_id = C.customer_id 
where  (Select count(FB.bet_id) From sportsbook_bets FB where FB.customer_id = C.customer_id and FB.sport_name = 'football'and FB.in_play_yn = 'Y') > 
        ((Select count(TB.bet_id) From sportsbook_bets TB where TB.customer_id = C.customer_id) / 50.0)

Other way that I like more it's to separate your calculations, for this you can use the APPLY operator:

Select  distinct 
            C.customer_id
From customers C
Join sportsbook_bets S On S.customer_id = C.customer_id
Cross Apply
(
    Select TotalBets = count(SB.bet_id)
    From sportsbook_bets SB
    where SB.customer_id = S.customer_id
) TB
Cross Apply
(
    Select FootballBets = count(SB.bet_id)
    From sportsbook_bets SB
    where SB.customer_id = S.customer_id
    and SB.sport_name = 'football'
    and SB.in_play_yn = 'Y'
) FB
where FB.FootballBets > TB.TotalBets / 50.0

CodePudding user response：

When performing a filter using an aggregate, need to use HAVING not WHERE.

Customers with >= 50% Bets on Football

SELECT c.customer_id
FROM customers c 
INNER JOIN sportsbook_bets s
    ON c.customer_id=s.customer_id
GROUP BY c.customer_id
HAVING COUNT(CASE WHEN s.sport_name = 'football' AND s.in_play_yn='Y' THEN 1 END)/1.0/COUNT(*) >= .50

If you want to see a row per bet for those the desired customers, could use this query instead:

WITH cte_CustomerBets AS (
    SELECT 
        c.customer_id
        ,s.sports_name
        /*Add any other columns you need as well*/
        ,COUNT(CASE WHEN s.sport_name = 'football' AND s.in_play_yn='Y' THEN 1 END) OVER (PARTITION BY CustomerID) AS TotalSportsBetsPerCustomer
        ,COUNT(*) OVER (PARTITION BY CustomerID) AS TotalBetsPerCustomer
    FROM customers c 
    INNER JOIN sportsbook_bets s
        ON c.customer_id=s.customer_id
    GROUP BY c.customer_id
    HAVING COUNT(CASE WHEN s.sport_name = 'football' AND s.in_play_yn='Y' THEN 1 END)/1.0/COUNT(*) >= .50
)
SELECT *
FROM cte_CustomerBets
WHERE TotalSportsBetsPerCustomer/1.0/TotalBetsPerCustomer >= .5

CodePudding user response：

For readability, you can separate the two components -- total bets and in-play football bets -- into two queries. This may not perform as quickly as other solutions here, but it may be easier to understand and maintain.

WITH totalbets AS (
  SELECT c.customer_id
  , count(s.bet_id) as TotalBets
  FROM customers c
    INNER JOIN sportsbook_bets s ON c.customer_id = s.customer_id
  GROUP BY c.customer_id
),
ipfbets AS (
  SELECT customer_id
  , count(bet_id) as IPFBets
  FROM sportsbook_bets
  WHERE sport_name = 'football'
    and in_play_yn = 'Y'
  GROUP BY customer_id
)

SELECT tb.customer_id
FROM totalbets tb
  INNER JOIN ipfbets ipfb ON tb.customer_id = ipfb.customer_id
WHERE (1.0 * ipfb.IPFBets) / tb.TotalBets > 0.5