data source calculated metrics VS SQL Aggregations

[DZlAmir]

Hello Guys I have a hard problem which I need your help in
We are trying to make one common place for all our metrics and there are two ways to do that

1. Either by using Looker studio data sources calculated metrics
2. Or by using directly the aggregation function on big query like (SUM, ...)

The second option have way more benefits for us but I have one problem with the second option

lets say a stakeholder want a daily and monthly chart for the number of users

1. with the first approach, I will do this

SELECT * FROM orders

and after that i will create a calculated filed called nbr_users

COUNT_DISINCT(userID)

 this way it will work perfectly with the first approach
e.g.: I have user order 1 time for 3 days in a row so when i do daily count i should see 1 user one each day and when i do monthly I get only 1 user (it works this way with the first approach)

2. but with the second approach the problem appear
i will do this query

SELECT
  COUNT(DISTINCT(userID)),
  DATE(creationDate) AS day
FROM
  orders
GROUP BY
 DATE(creationDate)

and then i do nothing on the data source i just connect it with this custom BigQuery SQL

 but this way when we get back to our example we will have an issue
on the daily we will get the user once daily for three days
but when we do monthly we will get 3 users but the truth its only one user that had orders on different days

The only way to fix this i can think about is doing different sql for each groupping (monthly or daily or weekly, ...)

but this way is hard to handle creating 5 sql just to do the same thing with different grouping

lets say stakeholders want daily, weekly, monthly, quarterly, and yearly its like 5 SQLs