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.
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.
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;