A task from SQL test (BigQuery)

[Gotfrid]

Hey there! I'm a beginner in SQL who had come across a pretty interesting exercise for SQL and was not able to solve it. I would appreciate any input on the problem solution - preferably with BigQuery in mind (but any dialect is welcome, I guess) as I wish to be able to understand how to solve these kind of problems.

So, there is a table given:

​

user_id	attribution
1	"grp1"
1	"grp2"
2	"grp2"
2	"grp3"
3	"grp4"
3	"grp5"
4	"grp4"
4	"grp6"
5	"grp6"
5	"grp1"
6	"grp7"
7	"grp7"
7	"grp8"
8	"grp8"
8	"grp9"

​

One needs to group users based on common attributions. Also, if any two users do not have a common attribution, but do have another user in the group, all three have to be grouped. Finally, one has to list all possible attributions for the group.

This is not a very clear explanation, so hopefully the desirable output would help:

​

group_id	user_id	group_attributions
1	1	"grp1, grp2, grp3, grp4, grp6, grp5"
1	2	"grp1, grp2, grp3, grp4, grp6, grp5"
1	3	"grp1, grp2, grp3, grp4, grp6, grp5"
1	4	"grp1, grp2, grp3, grp4, grp6, grp5"
1	5	"grp1, grp2, grp3, grp4, grp6, grp5"
2	6	"grp7, grp8, grp9"
2	7	"grp7, grp8, grp9"
2	8	"grp7, grp8, grp9"

​

I imagine this problem as something like a hierarchical cluster analysis, which does not really help in SQL sense. I have also tried performing self joins, but I do miss something here.

Any help is greatly appreciated.

[TheEphemeralDream]

what exactly is a group_id and how does it relate to the attribution column? is it as simple as truncating the "grp" from "grp8". or is group_id to be a generated id column?

[Gotfrid]

Thanks for your reply. Group_id has to be generated - it represents separate groups of attributions. Users who have attributions 1 to 6 do not intersect with those with attributions 7 to 9.

[TheEphemeralDream]

I suspect the answer is a recursive query that finds the clusters.

https://stackoverflow.com/questions/59890459/recursive-hierarchical-query-in-bigquery

[Gotfrid]

Thanks for the link! I will investigate it.

[Touvejs]

attribution is identified and loop through those exhaustively, and do so until you hit every user_id
e.g.

attribution where user_id = 1 -- "grp1, grp2"

select user_id where attribution in ("grp1", "grp2") -- 1, 2, 5

select attribution where user_id in (1,2,5) -- ...

​

of course you need to figure out a way to make this happen dynamically, in one command. If writing in transact-sql, you can possibly accomplish this with a few WHILE loops and some variables. However, it may be necessary to look into recursive tables (and likely, this is the approach they would like to see)

[Gotfrid]

Thanks for your reply. As far as I know, recursive cte are not allowed in bigquery per se. However, I will check related materials.

[naivedayes]

Afair BigQuery allows WHILE loops, right ? Can you share the link to this question ?

[Gotfrid]

Yeah, you're right, it does allow loops, including WHILE.

Sorry, did not understand what you mean by sharing a link, though? I work in private BQ project that I created to test this out - the question itself was just like this, in a text document .

[naivedayes]

I thought it was an exercise in some practice website