Dynamics behind using both Distinct and Group by

SQL++
1

Warm Greetings!

I am having trouble testing this functionality so am hoping for a little clarification.

Say i have an online store where different users (with unique ids) buy stuff on a daily. I’d like to see how many unique users have made an order each day over a month. My querry selects distinct user id’s and then groups by date. Here’s my question: if the same user comes in on two different days, will using the distinct function mean that user will only be counted on one of the dates?

here my querry, for reference:

select count(distinct user_id) as orders_made,split(details.time,“T”)[0] as date
from test where details.time like “2020-03%”
group by split(details.time,“T”)[0]
order by split(details.time,“T”)[0] desc

2

You are doing aggregate. With in the group aggregates are done.
i.e in this case same day same user_id counted once.

3

sorry for the late reply; what happens when the same id appears on two different dates? past attempts seem to point to same ID on different days is only counted on one of the days.

4

As you are grouping on day it counts on each day

1 Like