Demo prompts¶
This page describes prompts to be used in demos in the AP prod environment.
Data browser text-to-SQL queries¶
Select schema
PEPFAR.
Give me an overview of programs and their program stages and data elements. Include the data element value type and aggregation type. Order by program and program stage.
Give me the sum of data for HTS test and TX curr by quarter and OU national level. Show me the 50 highest values in descending order.
Give me the sum of data for HTS test and TX curr by quarter and OU national level. Include the data item. Include last 5 years. Show me the 50 highest values in descending order.
Give me a list of regions (OU national) and the number of sites (org units) which have at last 200 people on treatment, defined as TX CURR. Include the average number of people on treatment per org unit, rounded to nearest number.
Give me the sum of antenatal first, second and third visits by quarter and OU national level. Show me the 100 highest values in descending order.
Select reasoning level
THINKING.
Tell me about the most significant data outliers across time for Kenya.
* Use the "PT Quarterly" column as time dimension and "OU National" as country dimension.
* Use the Modified Z-Score statistical method for identifying outliers.
* Return up to the 200 most significant outliers.
* Order by Modified Z-Score largest value first.
Show me outlier data for facilities for Kenya for TX NEW.
* Facilities are at OU level 4, provinces are at OU subnational.
* Include data item, province, facility, quarter and data value.
* Find facilities where the TX NEW count in any single quarter of 2021 spiked by more than 2.5 std deviations compared to the trailing 8 quarter average.
Tell me the percentage of women who know their HIV status at antenatal care (PMTCT STAT) compared to antenatal first visits, by quarters of 2020 and 2021 at sub-national level for Tanzania.
Script editor text-to-Python scripts¶
Outlier detection
Write an outlier detection script that:
1. Retrieves data for HIV testing
2. Identifies outliers in the dataset
3. Prints the outliers to the console
* Use the following SQL query to retrieve data:
```
SELECT d.DataItem, d."PT Quarterly", d.OrgUnitID, d.OrgUnitName, d.Value
FROM pepfar.data d WHERE d.DataItem = 'HTS_TST_POS' LIMIT 20000;
```
* Use the "Modified Z-Score" statistical method for identifying the outliers.
* Return only up to the 200 most significant outliers.
* Print the outliers to the console as a table.
Data connector
Fetch indicator data from the World Bank Data API.
* Use these indicator codes: "NY.GDP.PCAP.KD", "NY.GDP.MKTP.KD.ZG", "SP.DYN.LE00.IN".
* Retrieve data for these country codes: "KEN", "TZA", "UGA".
* Render countries on columns, and indicators on rows. Sort data by year.
* Handle non-existing indicators.
* Print the data to the console as a table.
Forecasting
Select reasoning level
THINKING.
Write a data forecasting script that:
1. Retrieves data for HIV testing for the 8 quarters in 2020 and 2021
2. Forecasts data for the next 4 quarters for Tanzania
3. Prints the forecasted values to the console
* Use the following SQL query to retrieve data:
```
SELECT d.DataItem, d.OrgUnitID, d.OrgUnitName, d."PT Quarterly", d.Value
FROM pepfar.data d
WHERE d.DataItem = 'HTS_TST_POS'
AND d."PT Quarterly" IN ('2020Q1', '2020Q2', '2020Q3','2020Q4',
'2021Q1','2021Q2','2021Q3','2021Q4')
AND d."OU National" = 'Tanzania'
ORDER BY d."DataItem", d."OrgUnitID", d."PT Quarterly" LIMIT 20000;
```
* Use an ARIMA model from the statsmodels package.
* Providing data forecasts in time for the next 4 quarters of 2022 individually.
* Print the forecasted values to the console as a table.
Convo¶
Log in with username
bao-convo.
Tell me numbers for TX_NEW, TX_NET_NEW and TX_RTT for year 2020 for Tanzania, Kenya, Uganda and Mozambique only.