# How to get monthly pageviews programatically? Preferably using Data Explorer

**URL:** <https://meta.discourse.org/t/how-to-get-monthly-pageviews-programatically-preferably-using-data-explorer/178103>\
**Category:** Data & reporting\
**Tags:** sql-query\
**Created:** [February 2, 2021, 1:00pm UTC](https://meta.discourse.org/t/how-to-get-monthly-pageviews-programatically-preferably-using-data-explorer/178103 "2021-02-02T13:00:42Z")\
**Posts on this page:** 1\
**Showing post:** 7

<div class="post-metadata">

**Author:** ![michebs](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/michebs/32/190102_2.png) [@michebs](https://meta.discourse.org/u/michebs)\
**Post date:** [February 2, 2021, 2:59pm UTC](https://meta.discourse.org/t/how-to-get-monthly-pageviews-programatically-preferably-using-data-explorer/178103/7 "2021-02-02T14:59:18Z")

</div>

Yes, it is possible, in this case, you need to adjust the format of the date field first and then adjust the group by. Below is the adjusted query.

```sql
-- [params]
-- date :start_date
-- date :end_date

WITH data AS 
    (SELECT
        date_part('month', ar.date) AS month,
        date_part('year', ar.date) AS year,
        CASE 
          WHEN ar.req_type=6 THEN 'Crawlers'
          WHEN ar.req_type=7 THEN 'Logged in users'
          WHEN ar.req_type=8 THEN 'Anonymous users'
        END AS pageview,
        ar.count AS views
    FROM application_requests ar
    WHERE req_type IN (6,7,8)
        AND ar.date::date BETWEEN :start_date::date
    	AND :end_date::date
    ORDER BY ar.date ASC, ar.req_type
    )
    
  SELECT 
    month, 
    year, 
    sum(views) AS qtt_views
  FROM data 
  GROUP BY year, month
  ORDER BY year DESC, month ASC

```

| month | year | count |
| --- | --- | --- |
| 1 | 2021 | 1000 |
| 2 | 2021 | 500 |
| 1 | 2020 | 1500 |
| 2 | 2020 | 2000 |
| 3 | 2020 | 2500 |

---

_[View the full topic](https://meta.discourse.org/t/how-to-get-monthly-pageviews-programatically-preferably-using-data-explorer/178103)._