I am working with a table called "Campaigns". It has a bit of a complex schema:

The field "properties.status" can have different values - "opened", "clicked" and a few other values that are not relevant at the moment. Bringing context to my question - this is a table that stores information from user's activities on the emails that we send to them. Every time a user opens and clicks on an email, a row is created on the table. So if the user opens the same email 3 times, we will see 3 lines with the same information "properties.status = "opened".

I want to use just a few fields from this column, and most importantly I want to see how many USERS have opened and clicked each of our emails. Bring this to SQL, I would like to count the distinct "internal_customer_id" for when the status was "opened" and "clicked", grouped by Campaign Name. So what I'd like to see as an output is:

I did try to Pivot, as you can see on this question but the output is that for every opened and for every click, I have one line in the table where Opened = 1 and Clicked = 1.

CodePudding user response：

I think, you are looking for below

select * from (
  select UserID, 
    properties.CampaignName, 
    properties.Subject, 
    properties.Status 
  from your_table
)
pivot (count(distinct UserID) for Status in ('opened', 'clicked'))