sql finding cid with most expired cards-CodePudding

I have a table Cards(card_id,status,cid)

With the columns:

cid - customer id
status - exp/vld
card_id - card id's

How to find the cid with the most expired cards?

CodePudding user response：

From Oracle 12, you can use:

SELECT cid,
       COUNT(*) AS num_exp
FROM   cards
WHERE  status = 'exp'
GROUP BY cid
ORDER BY num_exp DESC
FETCH FIRST ROW WITH TIES;

CodePudding user response：

You can get count of expired cards for individual customers and then choose customer with MAX count. The below query should give results.

WITH t AS(
SELECT cid, count(1) customer_exp_cards_count
FROM Cards where status = 'exp'
group by cid)
SELECT cid FROM t t1 
WHERE t1.customer_exp_cards_count IN (SELECT MAX(t2.customer_exp_cards_count) 
                                      FROM t t2)

Sample data and its result: cardid status cid

3   exp 5
1   exp 1
2   exp 1
3   vld 1
5   vld 1
1   exp 2
2   exp 2
3   exp 2
4   vld 2
5   vld 2
6   exp 3
7   vld 4
4   vld 5

Result: 2

CodePudding user response：

Suppose you have these two tables (just a sample data)
CUSTOMERS

CUST_ID	CUST_NAME	CUST_STATUS
101	John	ACTIVE
102	Annie	ACTIVE
103	Jane	ACTIVE
104	Bob	INACTIVE

CARDS

CARD_ID	CARD_STATUS	CUST_ID
1001001	VALID	101
1001002	VALID	101
1001003	EXPIRED	101
1001004	EXPIRED	101
1001005	VALID	101
1002010	VALID	102
1002020	EXPIRED	102
1002030	EXPIRED	102
1002040	EXPIRED	102
1003100	VALID	103
1003200	VALID	103

If you want just a CUST_ID with the number of most expired cards you can do it without table CUSTOMERS:

Select  CUST_ID, EXPIRED_CARDS
From    (Select CUST_ID, Count(CARD_ID) "EXPIRED_CARDS" From cards Where CARD_STATUS = 'EXPIRED' Group By CUST_ID)
Where EXPIRED_CARDS = (Select Max(EXPIRED_CARDS) From (Select Count(CARD_ID) "EXPIRED_CARDS" From cards Where CARD_STATUS = 'EXPIRED' Group By CUST_ID) )
--  
--  R e s u l t
--     CUST_ID EXPIRED_CARDS
--  ---------- -------------
--         102             3

Maybe you could consider creating a CTE with the data from both tables which will give you dataset that you could use later for different questions not just for this one. Something like this:

WITH
  customers_cards AS
    (
        Select
            cst.CUST_ID,
            cst.CUST_NAME,
            cst.CUST_STATUS,
            crd.CARD_ID,
            crd.CARD_STATUS,
            Sum(CASE WHEN crd.CUST_ID Is Null Then 0 Else 1 End) OVER(Partition By crd.CUST_ID) "TOTAL_NUM_OF_CARDS",
            Sum(CASE WHEN crd.CARD_ID Is Null Then Null WHEN crd.CARD_STATUS = 'VALID' And crd.CARD_ID Is Not Null Then 1 Else 0 End) OVER(Partition By crd.CUST_ID) "VALID_CARDS",
            Sum(CASE WHEN crd.CARD_ID Is Null Then Null WHEN crd.CARD_STATUS = 'EXPIRED' And crd.CARD_ID Is Not Null Then 1 Else 0 End) OVER(Partition By crd.CUST_ID) "EXPIRED_CARDS"
        From
            customers cst
        Left Join
            cards crd on(crd.CUST_ID = cst.CUST_ID)
    )
/*  R e s u l t :
   CUST_ID CUST_NAME CUST_STATUS CARD_ID CARD_STATUS TOTAL_NUM_OF_CARDS VALID_CARDS EXPIRED_CARDS
---------- --------- ----------- ------- ----------- ------------------ ----------- -------------
       101 John      ACTIVE      1001001 VALID                        5           3             2 
       101 John      ACTIVE      1001002 VALID                        5           3             2 
       101 John      ACTIVE      1001003 EXPIRED                      5           3             2 
       101 John      ACTIVE      1001004 EXPIRED                      5           3             2 
       101 John      ACTIVE      1001005 VALID                        5           3             2 
       102 Annie     ACTIVE      1002010 VALID                        4           1             3 
       102 Annie     ACTIVE      1002040 EXPIRED                      4           1             3 
       102 Annie     ACTIVE      1002030 EXPIRED                      4           1             3 
       102 Annie     ACTIVE      1002020 EXPIRED                      4           1             3 
       103 Jane      ACTIVE      1003100 VALID                        2           2             0 
       103 Jane      ACTIVE      1003200 VALID                        2           2             0 
       104 Bob       INACTIVE                                         0                          
*/

This can be used to answer many more potential questions. Here is the list of customers sorted by number of expired cards (descending):

Select Distinct
    CUST_ID, CUST_NAME, TOTAL_NUM_OF_CARDS, VALID_CARDS, EXPIRED_CARDS
From
    customers_cards
Order By
    EXPIRED_CARDS Desc Nulls Last, CUST_ID
--  
--  R e s u l t :
--     CUST_ID CUST_NAME TOTAL_NUM_OF_CARDS VALID_CARDS EXPIRED_CARDS
--  ---------- --------- ------------------ ----------- -------------
--         102 Annie                      4           1             3 
--         101 John                       5           3             2 
--         103 Jane                       2           2             0 
--         104 Bob                        0

OR to answer your question:

Select Distinct
    CUST_ID, CUST_NAME, TOTAL_NUM_OF_CARDS, VALID_CARDS, EXPIRED_CARDS
From
    customers_cards
Where
    EXPIRED_CARDS = (Select Max(EXPIRED_CARDS) From customers_cards)
Order By
    CUST_ID
--  
--  R e s u l t :
--     CUST_ID CUST_NAME TOTAL_NUM_OF_CARDS VALID_CARDS EXPIRED_CARDS
--  ---------- --------- ------------------ ----------- -------------
--         102 Annie                      4           1             3

Regards...