endpoint to get statistics of geohub usage

[JinIgarashi]

https://undp.sharepoint.com/:x:/r/sites/SDGIntegrationteam/Shared%20Documents/SDG%20Metrics,%20Data%20and%20Analytics/6.%20Reporting/1.%20Results/Results%20%26%20Reporting.xlsx?d=wf251066802844f5690a5e22f64c5551e&csf=1&web=1&e=F8lqeE

[JinIgarashi]

-- 1.1 Total number of log-ins

SELECT count(*) FROM geohub.users where signupat > '2024-01-01'

-- 2.2 Number of user-uploaded and downloaded datasets

SELECT count(*) FROM geohub.dataset where createdat > '2024-01-01'

-- 2.2.1 By country

WITH country_tag as (
SELECT id, key, value
FROM geohub.tag
WHERE key = 'country'
)
SELECT count(*) 
FROM geohub.dataset a
INNER JOIN geohub.dataset_tag b
ON a.id = b.dataset_id
where createdat > '2024-01-01'
AND exists(select id FROM country_tag WHERE id = b.tag_id)

-- 2.2.2 By region

WITH country_tag as (
SELECT id, key, value
FROM geohub.tag
WHERE key = 'region' or key = 'continent'
)
SELECT count(*) 
FROM geohub.dataset a
INNER JOIN geohub.dataset_tag b
ON a.id = b.dataset_id
where createdat > '2024-01-01'
AND exists(select id FROM country_tag WHERE id = b.tag_id)