Hi Experts,
I do not know how to create one measure in logic layer based on the business requirement(Count the customer numbers for one time during a period of time).
It is very easy to write SQL in DB, but I do not know how to create model in Admin Tools.
BusinessDate : Customer Order Date
customerID : Customer ID
Platform : Customer Order Platform
counts: Customer Order Numers for one day and one platform
For example（Table）:
BusinessDate|customerID|Platform|counts
20171225|10001|A|1
20171225|10001|A|2
20171226|10001|A|3
SQL :
WITH a AS (
SELECT customerID , Platform , sum(counts) AS COUNTS FROM temp
WHERE BusinessDate BETWEEN '20171225' AND '20171230'
GROUP BY customerID , Platform
HAVING sum(counts)=1
)
SELECT Platform, COUNT(DISTINCT customerID) FROM a
GROUP BY Platform
PS: I find I only use one aggregation, not two aggregation (Firstly Sum, Secondly Count Distinct)
