Select the database you imported from the listing and your own warehouse. No fixed database name is assumed. Each download contains one read-only query, using the same SQL shown below.
Record RELEASE_ID and SNAPSHOT_DATE with your results.
Check the release and source
What this returns
Returns one accepted release with its source, licence, coverage and timestamps. Check these before combining directory results with other data.
Scroll SQL sideways if needed. Keyboard: focus the code and use the left and right arrow keys.
-- Select your imported childcare database and a warehouse before running.
-- Check the accepted release and its upstream source information.
SELECT s.RELEASE_ID, s.STATUS, s.GENERATION, s.SNAPSHOT_DATE,
s.ROW_COUNT, s.QUALITY_FLAG_COUNT,
s.SOURCE_UPDATE_FREQUENCY, s.SOURCE_LAST_MODIFIED,
s.INGESTED_AT, s.VALIDATED_AT, s.PUBLISHED_AT,
c.DATASET_NAME, c.SOURCE_URL, c.DICTIONARY_URL,
c.LICENCE_NAME, c.LICENCE_URL, c.ATTRIBUTION,
c.COVERAGE_NOTE, c.QUALITY_NOTE
FROM RELEASES.C10_FREE_DATASET_STATUS AS s
JOIN RELEASES.C10_FREE_DATASET_CATALOG AS c
ON c.RELEASE_ID = s.RELEASE_ID AND c.DATASET_ID = s.DATASET_ID
WHERE s.DATASET_ID = 'C10' AND s.STATUS = 'PUBLISHED';
Compare active licence counts by city
What this returns
Returns the 25 largest counts by the original city and province labels. These are licences marked Active at the snapshot date, not vacant places or normalized municipal boundaries.
Scroll SQL sideways if needed. Keyboard: focus the code and use the left and right arrow keys.
-- Counts of active licences in the accepted snapshot, not available places.
-- Preserve source city labels and include province to distinguish locations.
SELECT RELEASE_ID, SNAPSHOT_DATE, PROVINCE, CITY,
COUNT(*) AS ACTIVE_LICENCE_COUNT
FROM RELEASES.C10_FREE_DIRECTORY
WHERE LICENCE_STATUS = 'Active'
GROUP BY RELEASE_ID, SNAPSHOT_DATE, PROVINCE, CITY
ORDER BY ACTIVE_LICENCE_COUNT DESC, PROVINCE, CITY
LIMIT 25;
Explore source language labels
What this returns
Returns each distinct original service-language string and its record count. Combined labels remain intact; a multilingual record is counted once.
Scroll SQL sideways if needed. Keyboard: focus the code and use the left and right arrow keys.
-- Source language-of-service strings are kept intact.
-- A combined label is one source category, not separate translated records.
SELECT RELEASE_ID, SNAPSHOT_DATE, LANGUAGE_OF_SERVICE,
COUNT(*) AS LICENCE_COUNT
FROM RELEASES.C10_FREE_DIRECTORY
GROUP BY RELEASE_ID, SNAPSHOT_DATE, LANGUAGE_OF_SERVICE
ORDER BY LICENCE_COUNT DESC, LANGUAGE_OF_SERVICE;
Inspect a licence's quality flags
What this returns
Returns the chosen licence with each assigned flag. Licence 39013 has both the postal-code discrepancy and the QC civic-address flag; no source value is silently repaired.
Scroll SQL sideways if needed. Keyboard: focus the code and use the left and right arrow keys.
-- Licence 39013 illustrates a disclosed source postal-code discrepancy.
-- Change this text identifier to inspect another licence.
SELECT d.RELEASE_ID, d.SNAPSHOT_DATE, d.LICENCE_NUMBER,
d.SITE_NAME, d.LICENCE_STATUS, d.CITY, d.PROVINCE,
d.POSTAL_CODE, f.QUALITY_FLAG, d.SOURCE_WORKBOOK_SHA256
FROM RELEASES.C10_FREE_DIRECTORY AS d
LEFT JOIN RELEASES.C10_FREE_QUALITY_FLAGS AS f
ON f.RELEASE_ID = d.RELEASE_ID
AND f.DATASET_ID = d.DATASET_ID
AND f.LICENCE_NUMBER = d.LICENCE_NUMBER
WHERE d.LICENCE_NUMBER = '39013'
ORDER BY f.QUALITY_FLAG;
Use explicit columns in downstream applications. When querying multiple views, join by release and dataset identifiers as shown. Separate statements can observe different releases if publication occurs between them.