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

Postgresql – Postgres query to find multiple records with a particular repeat count within a table

I have 2 tables Customer and Orders.

1st question:

That is a master table for Customers that have a few columns like Customer number, customer name, Active flag, etc. Table may contain 2 or more records for the same customer number but as per the business logic only 1 records at a time should ideally be ACTIVE. I need to find customers that have only 1 record and it should be active.

query that I have written:

select customer_number, count(*) 
from customers c 
where active = false 
group by customer_number 
having count(*) = 1;

This returns me the customers that have 2 records and only 1 is NOT ACTIVE.

Question 2:

Apart from customer table we have another table that is Orders table, it contains columns like Customer number(as same in Customers table), deliver date, order number, insert time.
I need to find the customers whose ACTIVE is false, and have not given any orders since 180 days. (INSERT TIME::date – 180).

what I have tried is not giving me the desired output, as on back testing I found that the data is wrong

select om.customer_number, 
       c.customer_name, 
       om.deliverydate, 
       om.insert_time  
from customers c, order_master om 
where 
om.customer_number in 
   (
     select c2.customer_number  
     from customers c2 
     where c2.active = false 
     group by c2.customer_number 
    having count(*) =1
    ) 
and c.customer_number = om.customer_number 
group by om.customer_number, c.customer_name, 
         om.deliverydate, om.insert_time 
having max(om.insert_time::date) < '2022-06-01' ;

The queries that I have tried, I have already mentioned them in my question. Please check that.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    @Ahmed- Both of your queries worked fine.

    However in the 2nd query I want to fetch additional data into it, so what I did was -

    select om.customer_number, cu.customer_name, om.order_number ,om.insert_time 
from order_master om join 
  (
    select customer_number, customer_name
    from Customers c 
    group by customer_number, customer_name 
    having count(*) filter (where active) = 0
   ) cu
on om.customer_number = cu.customer_number
group by om.customer_number , cu.customer_name, om.insert_time,om.order_number 
 having max(om.insert_time) < current_date - interval '180 day';

When I tried the query shared by you -

        select om.customer_number
        from order_master om join 
      (
        select customer_number
        from Customers c 
        group by customer_number 
        having count(*) filter (where active) = 0
       ) cu
    on om.customer_number = cu.customer_number
    group by om.customer_number
    having max(om.insert_time) < current_date - interval '180 day';

    Its giving me around 4K results, and when I am trying with my modifications, so after adding each column in the query the result count is increasing exponentially till 75K and more.

    Also its showing me records for which max(om.insert_time) is much greater than 180 days


  2. 0

    For the first question, find customers that have only 1 record and it should be active , you may use conditional aggregation or filtered count as the following:

    select customer_number
from Customers c 
group by customer_number 
having count(*) = 1 and count(*) filter (where active) = 1;

    For the second question, find the customers whose ACTIVE is false, and have not given any orders since 180 days, try the following:

    select cu.customer_number
from order_master om join 
  (
    select customer_number
    from Customers c 
    group by customer_number 
    having count(*) filter (where active) = 0
   ) cu
on om.customer_number = cu.customer_number
group by cu.customer_number
having max(om.insert_time) < current_date - interval '180 day'

    See a demo.

    If you want to get all order details for the inactive customers, you may join the above query with the orders table as the following:

    with inactive_cust as
(
  select cu.customer_number, cu.customer_name
  from order_master om join 
  (
    select customer_number, customer_name
    from Customers c 
    group by customer_number, customer_name
    having count(*) filter (where active) = 0
   ) cu
  on om.customer_number = cu.customer_number
  group by cu.customer_number, cu.customer_name
  having max(om.insert_time) < current_date - interval '180 day'
)

select c.customer_number, c.customer_name,
       o.order_number, o.insert_time
from inactive_cust c join order_master o
on c.customer_number = o.customer_number

    See a demo.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search