Find users who make orders in specific month and also have orders in other months during a year-CodePudding

I have two tables:

Task:

Select last_name of users who:

placed at least one order in January 2013;
and also made at least one order in any other month of the same year.

I can only use subqueries, joins and unions (even WITH is not allowed).

I don't understand how to combine two conditions at the same time.

I ran this to get users with orders (invoices) in 2013:

SELECT
    customer_id,
    EXTRACT(MONTH FROM invoice_date::timestamp) AS invoice_month,
    COUNT(invoice_id) as invoices
FROM invoice
WHERE EXTRACT(YEAR FROM invoice_date::timestamp) = 2013
GROUP BY customer_id, invoice_month

Also I was able to find users who made at least one order in January 2013:

SELECT 
client.last_name
FROM client
WHERE client.customer_id IN (
SELECT
    customer_id
    FROM invoice
    WHERE 
        TO_CHAR(invoice_date::timestamp, 'YYYY-MM') = '2013-01'
    GROUP BY customer_id
    HAVING COUNT(invoice_id) >= 1
)

But I don't understand how to add second condition. Any tips?

CodePudding user response：

select last_name from
client left join
(
   select distinct customer_id from invoice where 
   month(invoice_date) = 1 and year(invoice_date) = 2013
) jan2013 on client.customer_id = jan2013.customer_id
left join
(
   select distinct customer_id from invoice where 
   month(invoice_date) != 1 and year(invoice_date) = 2013
) notjan2013 on jan2013.customer_id=notjan2013.customer_id
where notjan2013.customer_id is not null

CodePudding user response：

You could modify your query using the filtered count function as the following:

SELECT 
client.last_name
FROM client
WHERE client.customer_id IN (
 SELECT customer_id
 FROM invoice
 WHERE EXTRACT(YEAR FROM invoice_date::timestamp) = 2013
 GROUP BY customer_id
 HAVING COUNT(*) Filter (Where EXTRACT(MONTH FROM invoice_date::timestamp) = 1) >= 1
 And COUNT(*) Filter (Where EXTRACT(MONTH FROM invoice_date::timestamp) <> 1) >= 1
)