how to get the number of tests done for a county/region/state (does not matter)

[Aditya Bej]

there is no explicit column which gives number of tests like OOKLA open source data. Can someone guide me how to get this data.

[Lai Yi Ohlsen]

Hi Aditya, 

Lai Yi here from the M-Lab team. Because M-Lab publishes every result in a non-aggregated format, each row of data is a single test. You can get the test count by aggregating the results by region. 

For example to get median download and test count by state in the US, you could use this query: 

SELECT

client.Geo.Subdivision1Name as state,

APPROX_QUANTILES(a.MeanThroughputMbps, 100)[OFFSET(50)] AS median_download,

COUNT(*) as test_count

FROM `measurement-lab.ndt.ndt7`

WHERE date >= "2024-03-01"

AND client.Geo.CountryCode = "US"

AND client.Geo.Subdivision1Name IS NOT NULL

GROUP BY state

ORDER BY state

Or to get the test count of a particular Subdivision, you could use this query:

SELECT

APPROX_QUANTILES(a.MeanThroughputMbps, 100)[OFFSET(50)] AS median_download,

COUNT(*) as test_count

FROM `measurement-lab.ndt.ndt7`

WHERE date >= "2024-03-01"

AND client.Geo.Subdivision1Name = "New York"

Hope this helps!

On Fri, Mar 15, 2024 at 10:34 AM Aditya Bej <aditya...@gmail.com> wrote:

there is no explicit column which gives number of tests like OOKLA open source data. Can someone guide me how to get this data.

--
You received this message because you are subscribed to the Google Groups "discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email to discuss+u...@measurementlab.net.
To view this discussion on the web visit https://groups.google.com/a/measurementlab.net/d/msgid/discuss/a44f27fb-d995-4e93-a1ed-d807e1d894b6n%40measurementlab.net.