0

thanks for reading

I'm trying to use SQL to calculate percentages of counts

1) i need to figure out how many times a single person shops at the same store, in one city (london)- i've done this with a case when(london) - I need to figure out what percentage of users go to the same shop more than once

and 2) A second level showing - what percentage of users shop at the same store regularly (at least once Per month)

the code I've got so far is below - 

SELECT 
CASE WHEN COUNT(users) = 1 THEN "once"
WHEN COUNT(users)  > 1 THEN "multiple"
ELSE "NA"
END as userscountcheck,

COUNT(users) as singlemonthcount, users, city, businessname, CAST(concat(substr(CAST(date AS STRING), 1, 7), "-01") AS timestamp)  AS date_stamp

FROM fd_data.processing WHERE city LIKE'%London%' 
GROUP BY users, city, businessname, date_stamp
;

desired result would be:

30% of users have shopped at "TESCO" more than once
70% of users have shopped at "TESCO" only once
5% of users (of the 30%) have shopped at "TESCO" more than once per month

what would be the best way to show percentages grouped by city? should I do a nested count and group it by 'multiple' and 'once'? i'm struggling on how to calculate this and as i have 3 million+ records here, i can't extract it into excel and do it in there...

I appreciate your help very much in advance! thanks

EDIT:

Sample Data:

sample data

desired result is above

SS360
  • 63
  • 1
  • 6
  • Could you provide some sample data and execpt result? – D-Shih Mar 18 '19 at 15:14
  • @D-Shih - i've edited and attached the sample data. the desired result is shown above and it doesnt matter what format it is in, i'm unsure what the best way to show this would be.. i've been at it all day, tried PowerBI matrix but it did not work. thanks – SS360 Mar 18 '19 at 15:31

0 Answers0