I can only assume what they're trying to test, which are attributes of a user and performing counts on them. So they're testing whether or not you know the different joins (or specifically when to use an inner join and when to use a left/right join). It can be something like this Given the tables, I would left join them to a table that stores all users so that all users have attributes that correspond to a subpopulation. I would create a temp table out of this. I would then run my sql query based off this temp table so it'll look something like this: select subpopulation_A , count(distinct users) as count_of_users_in_subpopulation_A from temp_table group by 1 another way of writing it can be: select count(case when subpopulation_A notnull then user_id else null end) as count_subpop_A, count(case when subpopulation_B notnull then user_id else null end) as count_subpop_B, --etc...for all subpops from temp_table I'm assuming you're given a list of dimensional info (e.g., user_id) and their attributes (subpop categories). All you need to do is to create a table using joins (union won't work since they're different attributes) and then count them all. They seem to be testing on joins and aggregates functions.

If I understood your the problem that was given to you, you would have 2+ tables where the first table is the User table, while the others are the tables with specific activities (for example: groups they belong to, Likes per user and what they liked, etc). So the idea is to use left join and count. SELECT U.user_id, count( I.interest_id ) FROM t_user as U left JOIN t_interest as T ON U.user_id = T.user_id WHERE t.interest_id = subpopulation_you_want GROUP BY U.user_id

oops, it is: SELECT U.user_id, count( U.user_id)

All of the coding is on whiteboards which means you'll have to physically write all of your queries.

What is the answer to the SQL? Did the interviewer tell you the contents of tables?

I can feel your pain. I had same experience at Slack. After finishing take home coding assignment, I got moved to second round. Recruiter told me it will be a maths and statistics interview. I prepared for that and was in a different frame of mind. The interviewer throws a link at me with a tree question and tells me to start coding. WTH? I spent 4 days reviewing entire statistics/machine learning etc and you ask me a tree question? Down with you!