​
Overview

In this guide, you’ll learn how to use the Oxla database with Web Traffic datasets through various sample queries. These datasets contain essential traffic information, including user activity, device, and browser information. Data Source: https://github.com/ClickHouse/ClickBench .
Prerequisite
Ensure you’ve created the Data Storage with the appropriate demo dataset, the Cluster is running, and you’re connected to it using the PostgreSQL client. For more details, refer to the Quickstart - Oxla SaaS page.

​
Datasets Structure

Let’s explore the tables and their structures to understand better and fully utilize the Web Traffic dataset.

​
Sample Queries

​
Sample 1: Top 10 Pages by Hits

This query selects the top 10 pages based on the number of hits they received. 
SELECT title, COUNT(*) AS TotalHits
FROM hits
GROUP BY title
ORDER BY TotalHits DESC
LIMIT 10;
This result displays the top 10 pages along with the total number of hits each page received. 
                                                                            title                                                                             | totalhits 
--------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------
                                                                                                                                                              |  14910417
 Djunglish.RU > Приобрете и б/у. Стихи                                                                                                                        |   1652089
 Прог                                                                                                                                                         |   1158515
 Яндекс.Видео дня. Лента новый - сравных. Автопоиск по лучшей цены - Форум клуба Chevrolett, номера Соборудованном участные товая ул., д. 17, 3.5 - Свободная |    961538
 FLATY.RU - Лучшей цене, в пообедитино — Trashbox.ru#photograd.info: фут-фетиш, женщин - Яндекс.Афиша, 28 - Cherofloor=#ff99> <br>кто хорошее планшетные      |    832991
 Новостровск, ФОТО - Форум Амур.инфо, 8 из 15 ярко-розочках - сравнение                                                                                       |    809519
 Шарарту п.... Москве                                                                                                                                         |    730100
 Поиск по продажа                                                                                                                                             |    572331
 Участи NEWSru                                                                                                                                                |    486009
 Шарарансляция коду                                                                                                                                           |    420539
(10 rows)

​
Sample 2: Find Most Active Regions

This query extracts the most active regions ordered by the total number of hits. 
SELECT regionid, COUNT(*) AS TotalHits
FROM hits
GROUP BY regionid
ORDER BY TotalHits DESC
LIMIT 10;
This helps to pinpoint regions where the highest volume of interactions or activities has occurred. 
 regionid | totalhits 
----------+-----------
      229 |  18295832
        2 |   6687587
      208 |   4261812
      169 |   3320229
       32 |   1843518
       34 |   1792369
      184 |   1755192
       42 |   1542717
      107 |   1516690
       51 |   1435578
(10 rows)

​
Sample 3: Hourly Activity Analysis

This query uses the DATE_TRUNC function to aggregate user activity by hour. 
SELECT DATE_TRUNC('hour', EventTime) AS HourlyActivity,
COUNT(*) AS TotalHits
FROM hits
GROUP BY HourlyActivity
ORDER BY TotalHits DESC LIMIT 30;
The result displays the total hits aggregated by hour, which can help you determine the peak usage times. 
   hourlyactivity    | totalhits 
---------------------+-----------
 2013-07-21 00:00:00 |    698815
 2013-07-20 22:00:00 |    698653
 2013-07-20 20:00:00 |    697103
 2013-07-21 19:00:00 |    696980
 2013-07-20 21:00:00 |    696781
 2013-07-21 16:00:00 |    696039
 2013-07-21 15:00:00 |    694468
 2013-07-21 18:00:00 |    694037
 2013-07-21 12:00:00 |    693485
 2013-07-21 07:00:00 |    693146
 2013-07-21 04:00:00 |    693142
 2013-07-21 11:00:00 |    693106
 2013-07-21 13:00:00 |    692814
 2013-07-21 03:00:00 |    692689
 2013-07-21 05:00:00 |    692160
 2013-07-21 02:00:00 |    691713
 2013-07-21 17:00:00 |    691100
 2013-07-21 06:00:00 |    690457
 2013-07-21 10:00:00 |    689752
 2013-07-21 14:00:00 |    689493
 2013-07-20 23:00:00 |    689349
 2013-07-21 01:00:00 |    689090
 2013-07-21 09:00:00 |    688810
 2013-07-21 08:00:00 |    685090
 2013-07-09 18:00:00 |    573479
 2013-07-09 17:00:00 |    573035
 2013-07-08 21:00:00 |    572060
 2013-07-09 00:00:00 |    565040
 2013-07-08 22:00:00 |    564743
 2013-07-09 12:00:00 |    564698
(30 rows)