BigQuery - Unique Customers in Sales Table-CodePudding

I have sales table and I want to count unique customer from that sales table. I have to write query in BigQuery.

If you see in the given below Sales Table there are customers that visit the stores on different dates.

For example: Customer A visited Store A for 4 times from 01.01.2022 to 01.01.2023 therefore it is active 4 times but it is unique customer only in 01.01.2022 beccause he visits first time store later on customer A visits the same store multiple times therefore he is not unique anymore from 01.02.2022 to 01.01.2023 because it is already counted unique in 01.01.2022. Same scenarios for other customers.

Sales Table

CodePudding user response：

select count(distinct customer) from sales

To find date along with unique customer

Select store, customer, minimum (visit_date) group by customer, store

CodePudding user response：

select count(distinct customer)
from sales

Edit:

The original question was about counting unique customers. The above query will do that.

If you want to see other fields from the record of the first visit of a customer, then you use a window function like this:

select store, customer, visit_date
from (
    select store, customer, visit_date,
        row_number() over (partition by customer order by visit_date) n
    from sales
)
where n = 1