POST /v2/fhir/$import
Accept: text/yaml
Content-Type: text/yaml

id: myimp
contentEncoding: gzip
inputs:
- resourceType: Patient
  url: https://storage.googleapis.com/aidbox-public/events/2025-02-philippines-bootcamp/patient.ndjson.gz
- resourceType: QuestionnaireResponse
  url: https://storage.googleapis.com/aidbox-public/events/2025-02-philippines-bootcamp/questionnaireresponse.ndjson.gz
- resourceType: Observation
  url: https://storage.googleapis.com/aidbox-public/events/2025-02-philippines-bootcamp/observation.ndjson.gz

{}

GET /fhir/Observation/gen-obs-bmi-1
accept: text/yaml

meta:
  lastUpdated: '2025-01-28T14:56:39.967857Z'
  versionId: '81'
  extension:
  - {url: 'ex:createdAt', valueInstant: '2025-01-28T14:56:39.967857Z'}
derivedFrom:
- {reference: QuestionnaireResponse/gen-qr-1}
valueQuantity: {value: 24.064332204316088}
resourceType: Observation
effectiveDateTime: '2025-01-28T13:44:37.022833976Z'
status: final
id: gen-obs-bmi-1
code:
  coding:
  - {code: '60621009', system: 'http://snomed.info/sct', display: 'Body Mass Index '}

GET /fhir/Observation/gen-obs-smoking-1
Accept: text/yaml

meta:
  lastUpdated: '2025-01-28T14:56:39.967857Z'
  versionId: '81'
  extension:
  - {url: 'ex:createdAt', valueInstant: '2025-01-28T14:56:39.967857Z'}
derivedFrom:
- {reference: QuestionnaireResponse/gen-qr-1}
resourceType: Observation
effectiveDateTime: '2025-01-28T13:44:37.022833976Z'
status: final
id: gen-obs-smoking-1
code:
  coding:
  - {code: '365981007', system: 'http://snomed.info/sct', display: Tobacco smoking behavior - finding}
valueCodeableConcept:
  coding:
  - {code: '77176002', system: 'http://snomed.info/sct', display: Smoker}

GET /fhir/Observation/gen-obs-exc-alc-2

{
  "meta": {
    "lastUpdated": "2025-01-28T14:56:39.967857Z",
    "versionId": "81",
    "extension": [
      {
        "url": "ex:createdAt",
        "valueInstant": "2025-01-28T14:56:39.967857Z"
      }
    ]
  },
  "derivedFrom": [
    {
      "reference": "QuestionnaireResponse/gen-qr-2"
    }
  ],
  "resourceType": "Observation",
  "effectiveDateTime": "2025-01-28T13:44:37.022833976Z",
  "status": "final",
  "id": "gen-obs-exc-alc-2",
  "code": {
    "coding": [
      {
        "code": "719848005",
        "system": "http://snomed.info/sct",
        "display": "Disorder caused by alcohol"
      }
    ]
  },
  "valueCodeableConcept": {
    "coding": [
      {
        "code": "373067005",
        "system": "http://snomed.info/sct",
        "display": "No"
      }
    ]
  }
}

PUT /fhir/ViewDefinition/observation_bmi_view
Content-Type: text/yaml
Accept: text/yaml

name: observation_bmi_view

resource: observation
select:
  - column:
      - name: observation_id
        path: getResourceKey()
      - name: bmi
        path: value.ofType(Quantity).value
        type: decimal
      - name: qr_id
        path: derivedFrom.first().getReferenceKey()
where:
  - path: code.coding.where(code = '60621009').exists()

status: active
id: observation_bmi_view
resourceType: ViewDefinition

where:
- {path: code.coding.where(code = '60621009').exists()}
meta:
  lastUpdated: '2025-02-17T13:20:38.778225Z'
  versionId: '107'
  extension:
  - {url: 'https://fhir.aidbox.app/fhir/StructureDefinition/created-at', valueInstant: '2025-02-17T13:20:38.778225Z'}
name: observation_bmi_view
resourceType: ViewDefinition
status: active
id: observation_bmi_view
resource: observation
select:
- column:
  - {name: observation_id, path: getResourceKey()}
  - {name: bmi, path: value.ofType(Quantity).value, type: decimal}
  - {name: qr_id, path: derivedFrom.first().getReferenceKey()}

select * from sof.observation_bmi_view
limit 10

PUT /fhir/ViewDefinition/observation_smoking_view
Content-Type: text/yaml
Accept: text/yaml

name: observation_smoking_view
resource: observation

select:
  - column:
      - name: observation_id
        path: getResourceKey()
      - name: code
        path: value.ofType(CodeableConcept).coding.first().code
      - name: display
        path: value.ofType(CodeableConcept).coding.first().display
      - name: qr_id
        path: derivedFrom.first().getReferenceKey()
where:
  - path: code.coding.where(code = '365981007').exists()

status: active
id: observation_smoking_view
resourceType: ViewDefinition

where:
- {path: code.coding.where(code = '365981007').exists()}
meta:
  lastUpdated: '2025-02-17T13:20:47.141847Z'
  versionId: '110'
  extension:
  - {url: 'https://fhir.aidbox.app/fhir/StructureDefinition/created-at', valueInstant: '2025-02-17T13:20:47.141847Z'}
name: observation_smoking_view
resourceType: ViewDefinition
status: active
id: observation_smoking_view
resource: observation
select:
- column:
  - {name: observation_id, path: getResourceKey()}
  - {name: code, path: value.ofType(CodeableConcept).coding.first().code}
  - {name: display, path: value.ofType(CodeableConcept).coding.first().display}
  - {name: qr_id, path: derivedFrom.first().getReferenceKey()}

select * from sof.observation_smoking_view
limit 10;

PUT /fhir/ViewDefinition/observation_alcohol_view
Content-Type: text/yaml
Accept: text/yaml

name: observation_alcohol_view
resource: observation

select:
  - column:
      - name: observation_id
        path: getResourceKey()
      - name: code
        path: value.ofType(CodeableConcept).coding.first().code
      - name: display
        path: value.ofType(CodeableConcept).coding.first().display
      - name: qr_id
        path: derivedFrom.first().getReferenceKey()
where:
  - path: code.coding.where(code = '719848005').exists()

status: active
id: observation_alcohol_view
resourceType: ViewDefinition

where:
- {path: code.coding.where(code = '719848005').exists()}
meta:
  lastUpdated: '2025-02-17T13:21:02.093064Z'
  versionId: '113'
  extension:
  - {url: 'https://fhir.aidbox.app/fhir/StructureDefinition/created-at', valueInstant: '2025-02-17T13:21:02.093064Z'}
name: observation_alcohol_view
resourceType: ViewDefinition
status: active
id: observation_alcohol_view
resource: observation
select:
- column:
  - {name: observation_id, path: getResourceKey()}
  - {name: code, path: value.ofType(CodeableConcept).coding.first().code}
  - {name: display, path: value.ofType(CodeableConcept).coding.first().display}
  - {name: qr_id, path: derivedFrom.first().getReferenceKey()}

SELECT * FROM sof.observation_alcohol_view
LIMIT 10

PUT /fhir/ViewDefinition/qr_view
Content-Type: text/yaml
Accept: text/yaml

name: qr_view
resource: questionnaireresponse

select:
  - column:
      - name: qr_id
        path: getResourceKey()
      - name: gender
        path: >-
          item.where(linkId='Zicv0gIs').item.where(linkId='Jw_kMVir').answer.first().value.ofType(Coding).code
where:
  - path: questionnaire = 'http://forms.aidbox.io/questionnaire/cvd-form'

status: active
id: qr_view
resourceType: ViewDefinition

where:
- {path: 'questionnaire = ''http://forms.aidbox.io/questionnaire/cvd-form'''}
meta:
  lastUpdated: '2025-02-17T13:21:12.436424Z'
  versionId: '116'
  extension:
  - {url: 'https://fhir.aidbox.app/fhir/StructureDefinition/created-at', valueInstant: '2025-02-17T13:21:12.436424Z'}
name: qr_view
resourceType: ViewDefinition
status: active
id: qr_view
resource: questionnaireresponse
select:
- column:
  - {name: qr_id, path: getResourceKey()}
  - {name: gender, path: item.where(linkId='Zicv0gIs').item.where(linkId='Jw_kMVir').answer.first().value.ofType(Coding).code}

select * from sof.qr_view limit 10;

CREATE SCHEMA IF NOT EXISTS u_analytics;

CREATE OR REPLACE VIEW u_analytics.smoking AS
SELECT
	qr.gender AS gender,
    CASE
    	WHEN obs.code = '77176002' THEN 'Smoker'
        WHEN obs.code = '266919005' THEN 'Never smoked'
        WHEN obs.code = '160617001' THEN 'Stopped smoking'
        WHEN obs.code = '43381005' THEN 'Passive smoker'
    END AS status
FROM sof.observation_smoking_view obs
JOIN sof.qr_view qr USING (qr_id);

SELECT * FROM u_analytics.smoking LIMIT 10;

CREATE OR REPLACE VIEW u_analytics.alcohol AS
SELECT
	qr.gender AS gender,
    CASE
    	WHEN code = '373067005' THEN 'Not binge drinker'
        WHEN code = '373066001' THEN 'Binge drinker'
        ELSE 'Not drinker'
    END AS status
FROM sof.observation_alcohol_view obs
RIGHT JOIN sof.qr_view qr USING (qr_id);

SELECT * FROM u_analytics.alcohol LIMIT 10;

CREATE OR REPLACE VIEW u_analytics.bmi AS 
SELECT
	qr.gender AS gender,
    CASE
    	WHEN obs.bmi >= 25 THEN 'Overweight or obese'
        ELSE 'Normal weight'
    END status,
    obs.bmi as bmi
FROM sof.observation_bmi_view obs
JOIN sof.qr_view qr USING (qr_id);

SELECT * FROM u_analytics.bmi LIMIT 10;

select
	gender,
    status,
    count(*) * 100 / sum(count(*)) over () AS "percentage (total)",
    count(*) * 100 / sum(count(*)) over (PARTITION BY gender) as "percentage (group)"
FROM u_analytics.smoking
GROUP BY (status, gender)
ORDER BY gender asc, "percentage (group)" desc
limit 10;

select
	gender,
    status,
    count(*) * 100 / sum(count(*)) over () AS "percentage (total)",
    count(*) * 100 / sum(count(*)) over (PARTITION BY gender) as "percentage (group)"
FROM u_analytics.alcohol
GROUP BY (status, gender)
ORDER BY gender asc, "percentage (group)" desc
limit 10;

select
	gender,
    status,
    count(*) * 100 / sum(count(*)) over () AS "percentage (total)",
    count(*) * 100 / sum(count(*)) over (PARTITION BY gender) as "percentage (group)"
FROM u_analytics.bmi
GROUP BY (status, gender)
ORDER BY gender asc, "percentage (group)" desc
limit 10;