Sql- Count distinct values from date-30days to current register's date

Question

I have 1 table named "Transactions" with the following columns: Date, ClientID, Amount.

I would like to have the active clients in the last 30 days.

Something like this:

Date        |    Active_Clients
2017/08/10  |      697
2017/08/11  |      710
2017/08/12  |      689

etc

Meaning: From 2017/08/10 minus 30 days to 2017/08/10 I had 697 active users.

I tryed many ways and didnt make it.

Tag the dbms you're using. When it comes to date/time many products are far from ANSI SQL compliant. — jarlh, Aug 18 '17 at 13:00
In addition to jarlh's question, please show what have you tried so far. — Laposhasú Acsa, Aug 18 '17 at 13:01
Google "Count group by" and there will be thousands of examples for every RDMS. All you will need is, for SQL Server for example, is `where [Date} >= dateadd(day,-30,getdate())` — S3S, Aug 18 '17 at 13:07
Possible duplicate of [How to use count and group by at the same select statement](https://stackoverflow.com/questions/2722408/how-to-use-count-and-group-by-at-the-same-select-statement) — S3S, Aug 18 '17 at 13:08
@TimSchmelter This was my last try. The columns and tables are in portuguese select distinct data , (select distinct id_cliente, count(1) from appvitaminas..transacoes B group by id_cliente having b.Data between DATEADD(day,-30,a.Data) and A.Data ) Active_Clients from appvitaminas..transacoes A — Miguel Cunha, Aug 18 '17 at 14:41

score 1 · Answer 1 · answered Aug 18 '17 at 13:09

1

One method looks something like this:

select d.dte, count(distinct t.clientid)
from (select '2017-08-10' as dte union all
      select '2017-08-11' as dte union all
      select '2017-08-12' as dte 
     ) d left join
     transactions t
     on t.date <= d.dte and t.date > d.dte - interval '30' day
group by d.dte
order by d.dte;

The exact syntax for date constants, date arithmetic, and subqueries with constant values differs by database.

answered Aug 18 '17 at 13:09

Gordon Linoff

1,242,037
58
646
786

The dates given were an example. I want for all the dates in the table – Miguel Cunha Aug 18 '17 at 14:39

Maurício Pontalti Neri · Answer 2 · 2017-08-18T15:24:49.377

0

You can try this code to:

select distinct [Date]
, (select  count(distinct B.clientid) from  Transactions B 
    where b.Date between DATEADD(day,-30,a.Date) and A.Date) Active_Clients
from Transactions A

edited Aug 18 '17 at 15:24

answered Aug 18 '17 at 13:19

Maurício Pontalti Neri

1,060
1
10
17

Hey Mauricio, The problem here is that it's not taking into account the clienID meaning that the same client may be counted more than once if he went to the store multiple times! Correct me if i'm wrong – Miguel Cunha Aug 18 '17 at 14:37
If you use count(distinct B.clientid), you will only count the unique visits from the client. I updated the solution. – Maurício Pontalti Neri Aug 18 '17 at 15:26

score 0 · Answer 3 · answered Aug 18 '17 at 14:51

Thank you guys for your help.

I think I managed to find the answer with @gordonlinoff 's answer.

select distinct data , (select count(distinct id_cliente) from appvitaminas..transacoes B where b.Data between DATEADD(day,-30,a.Data) and A.Data) Active_Clients from appvitaminas..transacoes A

Sql- Count distinct values from date-30days to current register's date

3 Answers3