Show a count of distinct users for last week and current week in single query

Question

I would like to get the count of users for last week and this week in one table, but I am not familiar with how to do this.

All I have so far is below, using specific dates to extract the distinct count each week. Can someone show me how to do this, please?

Select
  COUNT(distinct f.user_key)
FROM
  `Analytics.warehouse.Fact_view` f
WHERE f.date BETWEEN '2023-11-06' AND '2023-11-12'

Answer 1

To have count of current week's data you can do as following:

Select COUNT(distinct f.user_key)
from Analytics.warehouse.Fact_view f Where f.date >= DATE_TRUNC(current_date(), WEEK) 

To get count of last week and this week data you can use below query:

Select count(distinct case when f.date<DATE_TRUNC(current_date(), WEEK) then f.user_key end) Last_week_count,
count(distinct case when f.date>=DATE_TRUNC(current_date(), WEEK) then f.user_key end) This_week_count

from Analytics.warehouse.Fact_view f
WHERE f.date >= DATE_SUB(DATE_TRUNC(current_date(), WEEK), INTERVAL 1 WEEK)