User profiling is a very common type of data analysis, which helps you segment a user base into fractions to learn and understand customer type and behavior.  

For this exercise, we will take a look at user actions across the webpage and bucket them into categories, which may be used for additional deep-dive analysis, A/B testing, or user research later on.

Let’s say our task here is to write a SQL query to locate users in a database who purchased specific items in our web store. Additionally, we want to compare 2 different segments for user profiles. For example, we want to identify (1) users who purchase many scarfs and (2) users who buy fewer scarfs and compare them to (3) users who purchase many hats and (4) fewer hats. How do we do it?   

Step 1.

We start by defining the audience for the first user segment. Let’s get all their relevant purchases for the item, which in our case is “scarf ”. Then filter out to the appropriate number of purchased items (assuming 50 scarfs is “fewer”). 

Step 2.

Now we define the second user profile who purchased many scarfs using the logic above. Let’s assume “many” is more than 50: 

Step 3.

For the third user segment, we have to pick clients who purchase many hats. Let’s make it harder, and assume there is no tag table which simply gives us the item type. Knowing item ids for hats, we still can filter it out for our profiling task:

Do the same for user segment #4, but specify the number of purchased items.

Step 4.

Final step. Let’s connect all those profiles together in the final SELECT statement:

There you go! All 4 profiles are ready for your analysis or experiment now. Instead of counts, you can extract a list of user_id into another VIEW or TABLE or a report export if needed.

Check the full code here.