SQL - Unique results in column A based on a specific value in column B being the most frequent value

user_19235357 0
Viewed 25

So I have the following challenge:

I'm trying to get unique results from all the clients (Column A) that made most of their purchases at store 103 (Column B).

The store is defined in the first 3 digits of the ticket number. The challenge is that I'm also getting every ticket for each client. And I just need SQL to calculate and filter the results, based on all the unique clients that made most of their purchases at store 103.

The information in Column A comes from Table 1 and the information in column B comes from Table 2.

Example

I've been trying the following:

SELECT DISTINCT Table_1.Full_Name, Table_2.Ticket_#
FROM Table_2
LEFT OUTER JOIN Table_1
  ON Table_2.Customer_Number = Table_1.Customer_Number;

I know I'm missing either the group by or order by keywords, but I don't know how to use them properly in this particular case.

Thank you very much in advance.

unique sql
1 Answers

Here are three options.

SELECT customers.Full_Name, tickets."Ticket_#"
FROM Table_2 tickets INNER JOIN Table_1 customers
    ON customers.Customer_Number = tickets.Customer_Number INNER JOIN
    (
        SELECT Customer_Number
        FROM Table_2 tickets
        GROUP BY Customer_Number
        HAVING COUNT(CASE WHEN LEFT("Ticket_#", 3)  = '103' then 1 end)
             > COUNT(CASE WHEN LEFT("Ticket_#", 3) <> '103' then 1 end)
    ) AS m ON m.Customer_Number = customers.Customer_Number

SELECT customers.Full_Name, tickets."Ticket_#"
FROM Table_2 tickets INNER JOIN Table_1 customers
    ON customers.Customer_Number = tickets.Customer_Number
WHERE customers.Customer_Number IN (
    SELECT Customer_Number
    FROM Table2 tickets
    WHERE "Ticket_#" LIKE '103%'
    GROUP BY Customer_Number
    HAVING COUNT(*) > (
        SELECT COUNT(*)
        FROM Table2 tickets2
        WHERE tickets2.Customer_Number = tickets.Customer_Number
            AND NOT "Ticket_#" LIKE '103%' 
    )
)

WITH data AS (
    SELECT customers.Full_Name, tickets."Ticket_#"
        COUNT(CASE WHEN LEFT(tickets."Ticket_#", 3)  = '103' then 1 end)
            OVER (PARTITION BY customers.Customer_Number) AS MatchCount
        COUNT(CASE WHEN LEFT(tickets."Ticket_#", 3) <> '103' then 1 end)
            OVER (PARTITION BY customers.Customer_Number) AS NonmatchCount
    FROM Table_2 tickets INNER JOIN Table_1 customers
        ON customers.Customer_Number = tickets.Customer_Number
)
SELECT * FROM data WHERE MatchCount > NonmatchCount;
user_13222680
Related