Query Examples

This section collects some typical examples of queries that return useful results. By using these examples as a starting point, you can customize and build upon them to suit your specific needs and requirements.

Feel free to experiment and iterate with these queries and contribute your templates by sending a Pull Request to this documentation page.

Your contributions will be greatly appreciated!

Total number of Data Messages

Returns the total number of data messages received by a certain W3bstream project.

SELECT project, SUM(value) AS total_messages
FROM ws_metrics.inbound_events_metrics
WHERE (project = 'eth_0x2c37a2cbcfaccdd0625b4e3151d6260149ee866b_bike_sharing')
GROUP BY project;

Daily Active Devices

Returns the number of devices that sent at least one message to a project.

SELECT toDate(time) AS date,  project, COUNT(DISTINCT publisher) AS daily_total_messages
FROM ws_metrics.inbound_events_metrics
WHERE (project = 'eth_0x2c37a2cbcfaccdd0625b4e3151d6260149ee866b_bike_sharing')
GROUP BY date, project;

Devices Growth

Returns the daily growth of the number of devices that are registered for a project.

SELECT project, toDate(time) AS date, SUM(value) AS total
FROM ws_metrics.publishers_metrics
WHERE (project = 'eth_0x2c37a2cbcfaccdd0625b4e3151d6260149ee866b_bike_sharing')
GROUP BY project, date
ORDER BY date DESC;

Custom Data Metrics

Returns the 10 most recent custom metrics generated by a certain project.

SELECT *
FROM ws_metrics.customized_metrics 
WHERE (project = 'eth_0x2c37a2cbcfaccdd0625b4e3151d6260149ee866b_bike_sharing')
ORDER BY time DESC
LIMIT 10