At this point, we've explored structuring both discrete and aggregate analytics data, and looked at accessing that data. However, to have interesting data aggregates to access, we first need to record our observations.
The daily_status_update_views table introduces a new type of column: the counter column. Counter columns store integer values, just like int and bigint columns; however, unlike a normal data column, counter columns are always incremented or decremented, rather than having a value set directly.
We've currently got two tables to store usage data: status_update_views to store raw view observations, and daily_status_update_views to store views by day. We'd like to record that one of alice's status updates was viewed on the web on October 5, 2014 at 3:12 P.M. EDT:
INSERT INTO "status_update_views" ( "status_update_username", "status_update_id", "observed_at", "client_type" ) VALUES ( 'alice', 76e7a4d0-e796-11e3-90ce-5f98e903bf02...