Query library¶

Welcome to the SQL query library for ClickHouse.

Introduction¶

The schema schema and base table name base in queries must be updated to match the relevant environment. Note that the schema and base table name are specified in dbt syntax.

DHIS2¶

Data elements with data set membership¶

SELECT
  de."Name" as "DataElementName",
  arrayStringConcat(groupArray(ds."Name"), ', ') AS "DataSetName"
FROM
  {{ schema }}."{{ base }}_datasetelement" dse
  INNER JOIN {{ schema }}."{{ base }}_dataset" ds on dse."DataSetID" = ds."ID"
  INNER JOIN {{ schema }}."{{ base }}_dataelement" de on dse."DataElementID" = de."ID"
GROUP BY
  de."Name"
ORDER BY
  de."Name";

Data value count by data element¶

SELECT
  de."Name" AS "DataElementName",
  ds."Name" AS "DatasetName",
  count(*) AS "DataValueCount"
FROM
  {{ schema }}."{{ base }}_datavalue" dv
  INNER JOIN {{ schema }}."{{ base }}_dataelement" de on dv."DataElementID" = de."ID"
  INNER JOIN {{ schema }}."{{ base }}_datasetelement" dse on de."ID" = dse."DataElementID"
  INNER JOIN {{ schema }}."{{ base }}_dataset" ds on dse."DataSetID" = ds."ID"
GROUP BY
  de."Name",
  ds."Name"
ORDER BY
  "DataValueCount" desc;

Aggregation of data items by quarterly periods and region org unit level¶

SELECT
  "PT Quarterly",
  "OU Region",
  "DataItem",
  sum("Value") AS "Value"
FROM
  {{ schema }}.{{ base }} ax
WHERE
  "DataItem" LIKE '%TX_CURR%'
  AND "PT Yearly" IN ('2024Q1', '2024Q2', '2024Q3', '2024Q4')
GROUP BY
  "PT Quarterly",
  "OU Region",
  "DataItem"

Aggregation of data items for last 5 years order by aggregate value¶

SELECT
  "PT Quarterly",
  "OU Region",
  "DataItem",
  sum("Value") AS "Value"
FROM
  {{ schema }}.{{ base }}
WHERE
  "DataItem" LIKE '%TX_CURR%'
  AND "PT Yearly" IN (
    SELECT
      "ID"
    FROM
      {{ schema }}."{{ base }}_relative_yearly_period"
    WHERE
      "Index" <= 5
  )
GROUP BY
  "PT Quarterly",
  "OU Region",
  "DataItem"
ORDER BY
  "Value" DESC

Category combos with identical categories¶

WITH
  fingerprint AS (
    SELECT
      "CategoryComboID",
      arraySort (groupArray ("CategoryID")) AS "CategoryFingerprint"
    FROM
      {{ schema }}."{{ base }}_categorycombos_categories"
    group by
      "CategoryComboID"
  )
SELECT
  "CategoryFingerprint",
  count() AS "Occurrence",
  groupArray ("CategoryComboID") AS "DuplicateCategoryCombos"
FROM
  fingerprint
GROUP BY
  "CategoryFingerprint"
HAVING
  "Occurrence" > 1;

Category options with identical category options¶

WITH
  fingerprint AS (
    SELECT
      "CategoryID",
      arraySort(groupArray("CategoryOptionID")) AS "CategoryOptionFingerprint"
    FROM
      {{ schema }}."{{ base }}_categories_categoryoptions"
    GROUP BY
      "CategoryID"
  )
SELECT
  "CategoryOptionFingerprint",
  count() AS "Occurrence",
  groupArray("CategoryID") AS "DuplicateCategories"
FROM
  fingerprint
GROUP BY
  "CategoryOptionFingerprint"
HAVING
  "Occurrence" > 1;

Category option combos with identical category options¶

WITH
  fingerprint AS (
    SELECT
      "CategoryOptionComboID",
      arraySort(groupArray("CategoryOptionID")) AS "OptionFingerprint"
    FROM
      {{ schema }}."{{ base }}_categoryoptioncombos_categoryoptions"
    GROUP BY
      "CategoryOptionComboID"
  )
SELECT
  "OptionFingerprint",
  count() AS "Occurrence",
  groupArray("CategoryOptionComboID") AS "DuplicateCategoryOptionCombos"
FROM
  fingerprint
GROUP BY
  "OptionFingerprint"
HAVING
  "Occurrence" > 1;