Postgres query to find multiple records with a particular repeat count within a table-CodePudding

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.

CodePudding user response：

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.

CodePudding user response：

@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