1. Number of publications added to Dimensions each month

Level: Easy

This query is suitable for new users of Dimensions on Google BigQuery

Description

Counts the number of publications added to Dimensions each month, using the date_inserted field.

Query

SELECT
  DATE_TRUNC(date_inserted, MONTH) as date,
  COUNT(id) as countDim
FROM
  `dimensions-ai.data_analytics.publications`
GROUP BY date
ORDER BY date DESC
LIMIT 5

Breaking it down

The DATETIME_TRUNC function is used here to "round down" the timestamps in the date_inserted field to the month level.

Results

[
  {
    "date": "2021-04-01 00:00:00 UTC",
    "countDim": "458175"
  },
  {
    "date": "2021-03-01 00:00:00 UTC",
    "countDim": "746884"
  },
  {
    "date": "2021-02-01 00:00:00 UTC",
    "countDim": "661512"
  },
  {
    "date": "2021-01-01 00:00:00 UTC",
    "countDim": "687725"
  },
  {
    "date": "2020-12-01 00:00:00 UTC",
    "countDim": "828301"
  }
]