Daniel Richter Daniel Richter - 1 year ago 44
SQL Question

Assigning a field value to all uniques in a table

I have an analytics table with the following fields:




An analytics record is created for every page a user visits. The question I would like to answer is this: How much revenue is coming from users that have been to a maps screen (pagename=mapview) versus users that have not. The revenue is only recorded when the user hits a page with a transactional element. I'm not keeping track of whether the user has been to a maps view once they hit a page with transaction elements

Do I need to create a separate table that tracks whether a particular user (unique_id) has been to a map screen and then join this with the original table? Or is there an easier way?

Answer Source

You can do this with aggregation -- two levels of aggregation:

select isMapView, sum(revenue), count(*) as numUsers
from (select unique_id, sum(revenue) as revenue,
             max(case when pagename = 'mapview' then 1 else 0 end) as isMapView
      from t
      group by unique_id
     ) u
group by isMapView;