Question 90397 in PostgreSQL Question posted in
The official documentation can be found here.

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

I have two tables:

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?

Tags:

2

Answers


  1. 0

    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
)
    Login or Signup to reply.
  2. 0 
    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search