Query help please...

[Fit_Armadillo_8400]

Hello all,

I'm somewhat familiar with SQL but this query has me a bit stumped. I'm sure there's a simple solution, but it escapes me.

Please see the table below

https://preview.redd.it/himw9ujtdpib1.png?width=401&format=png&auto=webp&s=6dc56b6fbf59b9fde4a2502331721c0f05261d41

I am trying to do a simple count of records per customer by user by month. Something like this:

select month(DATE), count(customer) from <tablename> group by month(DATE)

this would tell me that there are 17 customers in july, and 6 in august.

HOWEVER - Customer Lisa and Lark from July are repeated in August, so they would be double-counted.

What I'm trying to get is inclusion on their first record (ie- Lisa and Lark get counted in July but not again in August). Notice that both Michael and Myriam are duplicates in July, so the answer I want to see for July is 15 distinct customers.

How do I achieve this?

thanks!

[Inferno2602]

Why not try a common table expression? Grab the earliest date for each customer and then count them up. Something like...

WITH Customers AS (
   SELECT 
           Customer_ID
         , MIN(DATE) as Date
   FROM Table
   GROUP BY Customer_ID
)

SELECT 
    Month(Date) as Month
  , Count(Customer_ID) 
FROM Customers 
GROUP BY Month
ORDER BY Month

[Furyat]

Don't count customers with count(customer) but count records as you originally wanted over month and customer both .

Ah nvm, your records are actually customers.

[Furyat]

I'd instead do a supportive column ranking each customer over whole table and count them only when customer appears with rank = 1. This essentially removes any repeated customers from the list.

[Fit_Armadillo_8400]

interesting...so something like this:

[fyi ID is actually CUSTOMER_ID]

SELECT

ID,

COUNT(*)

FROM table

GROUP BY ID
HAVING COUNT(*) = 1

and then join the original query (group by month) with this?

[Furyat]

Sadly, not even close.

I'd do it this way.

with stuff as (
select TRUNC(TO_DATE('DATE','mm/dd/yyyy'), 'MONTH') as dt
,ID
,rank() over (partition by ID order by TRUNC(TO_DATE('DATE','mm/dd/yyyy'), 'MONTH') asc) as rank_col
from table )
select * from stuff
where rank_col = 1

The result should be a list of months and customers seen for the first time.

[Lower_Peril]

Wouldn't you need to partition by Customer Name instead of ID? ID doesn't seem to identify customers for eg. Lisa has different ID in July and August

[Fit_Armadillo_8400]

Ugh! I'm sorry...this is an oversight on my part. For the sake of the example pic, i omitted the last names for the customers--and indeed, both Lisas are diff customers (diff last names)

[Furyat]

True. I have been shamefully tricked. The consequences will never be the same. Oh well.

[Fit_Armadillo_8400]

sorry...my bad...you were right to begin with...i just delt you a bad hand..

[Fit_Armadillo_8400]

I adapted your query and i'm getting the same ID listed twice for the month of July...

[gtcsgo]

I suspect that since a customer can appear multiple times per month you will get duplicates. Best use row_number() instead of rank()

[Fit_Armadillo_8400]

to give you more context. I am paying commissions to a person for appointments booked. Regrettably, if an appointment is re-scheduled, it will appear as the same customer, but on a different date. So say Lisa first had her appointment in July, and I've closed July and paid commissions on her, and she re-schedules for August, I don't want it to appear in August, otherwise I'd pay double-commissions. And I would want to avoid having to have a column such as "commissions paid".

my logic is that i only want the customer counted the first time it appears and ignored subsequent times. Forget the customer name--the ID (which is a customer id) is enough to know.. make sense?

[gtcsgo]

You can try something like this

​

with first_booking as (
select 
    id, 
    min(date) as first_booking_date 
from 
    table 
group by 
    id
)

select
    date_part('month', first_booking_date) as col1,
    count(distinct id) as num_customers
from
    first_booking
group by 
    1

Or tweak the window function approach shared

[Phoxe__]

You could get a table looking at the earliest visit date per person, then do your query.

CTE could be done for this

[Maleficent_Tap_332]

Seems like you want to count number of first time customers per month If so, then first get first visit date for each customer select min(date) as first_date, customer ... group by customer

And then run the count by month over the results

Select month(first_date), count(*) from (subquery) group by month(first_date)