Supposed I have a data set that looks like this
{"id":15,"classification":"goth","categories":["blackLipstick","hotTopic"]}
{"id":14,"classification":"goth","categories":["drinking","girls","hotTopic"]}
{"id":13,"classification":"jock","categories":["basketball","chicharones","fooball","girls","pop","pregnant","sports","starTrek","tortilla","tostada"]}
{"id":12,"classification":"geek","categories":["academics","cacahuates","computers","glasses","papas","physics","programming","ps4","science"]}
{"id":11,"classification":"geek","categories":["cacahuates","fajitas","math","pregnant","raves","xbox"]}
{"id":10,"classification":"goth","categories":["cutting"]}
{"id":9,"classification":"geek","categories":["cafe","chalupa","chimichangas","manson","physics","pollo","tostada"]}
{"id":8,"classification":"jock","categories":["basketball","chalupa","enchurrito","piercings","running","sports"]}
{"id":7,"classification":"geek","categories":["aguacate","blackLipstick","computers","fajitas","fooball","glasses","lifting","outdoors","physics","pollo","pregnant","ps4"]}
{"id":6,"classification":"none","categories":["brocode","girls","raves","tacos"]}
{"id":5,"classification":"goth","categories":["blackLipstick","blackShirts","drugs","mole","piercings","tattoos","tortilla"]}
{"id":4,"classification":"jock","categories":["girls","tattoos"]}
{"id":3,"classification":"goth","categories":["girls"]}
{"id":2,"classification":"none","categories":["cutting","enchurrito","fooball","pastel","pregnant","tattoos","vampires"]}
{"id":1,"classification":"goth","categories":["cacahuates","cutting","drugs","empanadas","frijoles","manson","nachos","outdoors","piercings","tattoos"]}
{"id":0,"classification":"geek","categories":["pollo","pop","programming","science"]}
How do I write a query to where I can say "If someone has category 'math' what other categories do they often have?"
For this data set I can write something like this to tell me what goths, geeks and jocks like most.
SELECT classification, categories, count(categories) C
FROM [xx.stereotypes] group by classification
, categories ORDER BY C DESC LIMIT 1000
But in my real dataset I don't have the classification field. I want a query that could make help me create classifications like "goth", "jock" or "geek".
For example how do I say select all the counts of the categories where categories contains "math", this only selects math
SELECT categories, count(categories) C FROM [xx.stereotypes]
where categories CONTAINS "math" group by categories ORDER
BY C DESC LIMIT 1000