For some reason, I cannot save a question once I have pasted the below query insâŚide. Thanks!!
```sql
WITH
conditions_by_patient AS
(
SELECT
sender_id AS patient_id,
accounts_account.date_joined AS patient_join_date,
BOOL_OR(text ILIKE '%hiv%')::int AS HIV,
BOOL_OR(text ILIKE '%do not sleep%')::int AS sleep_issue,
BOOL_OR(text ILIKE '%do not get as much exercise%')::int AS exercise_issue,
BOOL_OR(text ILIKE '%do not eat as healthfully%')::int AS diet_issue,
BOOL_OR(text ILIKE '%high cholesterol%')::int AS high_colesterol,
BOOL_OR(text ILIKE '%high blood pressure%')::int AS high_blood_pressure,
BOOL_OR(text ILIKE '%peyronie%')::int AS peyronie,
BOOL_OR(text ILIKE '%stomach, intestinal, or bowel ulcers%')::int AS ulcers,
BOOL_OR(text ILIKE '%heart attack or heart disease at%')::int AS family_heart_issue,
BOOL_OR(text ILIKE '%liver disease%')::int AS liver_disease,
BOOL_OR(text ILIKE '%smoke tobacco%')::int AS smoke_tobacco,
BOOL_OR(text ILIKE '%prior heart attack, heart failure, or narrowing%')::int AS prior_heart_issue,
BOOL_OR(text ILIKE '%drink more than I should%')::int AS drinking_issue,
BOOL_OR(text ILIKE '%overweight%')::int AS overweight,
BOOL_OR(text ILIKE '%angina%')::int AS angina_or_shortness_of_breath,
BOOL_OR(text ILIKE '%diabetes%')::int AS diabetes,
BOOL_OR(text ILIKE '%arrhythmia%')::int AS arrhythmia,
BOOL_OR(text ILIKE '%alpha blocker%')::int AS alpha_blocker,
BOOL_OR(text ILIKE '%idiopathic hypertrophic subaortic stenosis%')::int AS ihss,
BOOL_OR(text ILIKE '%fibrous tissue in the penis%')::int AS fibrous_penis,
BOOL_OR(text ILIKE '%unexplained fainting, lightheadedness, or dizziness%')::int AS unexplained_fainting_etc,
BOOL_OR(text ILIKE '%Isosorbide mononitrate or Isosorbide dinitrate%')::int AS isosorbide_md,
BOOL_OR(text ILIKE '%pulmonary hypertension%')::int AS pulm_hypertension,
BOOL_OR(text ILIKE '%abnormal heart beats%')::int AS abnormal_heartbeats,
BOOL_OR(text ILIKE '%pain with erections or with ejaculation%')::int AS penis_sex_pain,
BOOL_OR(text ILIKE '%claudication%')::int AS claudication,
BOOL_OR(text ILIKE '%foreskin that is too tight%')::int AS foreskin_too_tight,
BOOL_OR(text ILIKE '%surgery or radiation to the prostate or pelvis%')::int AS prost_pelv_surg_rad,
BOOL_OR(text ILIKE '%multiple sclerosis (MS) or similar disease, spinal injuries or paralysis, or neurological diseases%')::int AS ms_spine_neural_issue,
BOOL_OR(text ILIKE '%nitroglycerin%')::int AS nitroglycerin,
BOOL_OR(text ILIKE '%advised not to have sex%')::int AS advised_no_sex,
BOOL_OR(text ILIKE '%nitrates%')::int AS nitrates
FROM
chat_api_message
JOIN chat_api_thread ON (chat_api_message.thread_id = chat_api_thread.id)
JOIN accounts_account ON (chat_api_message.sender_id = accounts_account.id)
WHERE
chat_api_thread.type = 'consultation'
AND accounts_account.role = '00_patient'
AND meta IS NOT NULL
AND meta != ''
AND meta LIKE '{%'
AND meta LIKE '%positive%'
AND text NOT ILIKE '%none%'
GROUP BY 1,2
)
SELECT
DATE_TRUNC('month', convert_timezone('America/New_York', patient_join_date)) AS join_period
, AVG(1.0*hiv) AS pct_with_hiv
, AVG(1.0*sleep_issue) AS pct_with_sleep_issue
, AVG(1.0*exercise_issue) AS pct_with_exercise_issue
, AVG(1.0*diet_issue) AS pct_with_diet_issue
, AVG(1.0*high_colesterol) AS pct_with_high_colesterol
, AVG(1.0*high_blood_pressure) AS pct_with_high_blood_pressure
, AVG(1.0*peyronie) AS pct_with_peyronie
, AVG(1.0*ulcers) AS pct_with_ulcers
, AVG(1.0*family_heart_issue) AS pct_with_family_heart_issue
, AVG(1.0*liver_disease) AS pct_with_liver_disease
, AVG(1.0*smoke_tobacco) AS pct_with_smoke_tobacco
, AVG(1.0*prior_heart_issue) AS pct_with_prior_heart_issue
, AVG(1.0*drinking_issue) AS pct_with_drinking_issue
, AVG(1.0*overweight) AS pct_with_overweight
, AVG(1.0*angina_or_shortness_of_breath) AS pct_with_angina_or_shortness_of_breath
, AVG(1.0*diabetes) AS pct_with_diabetes
, AVG(1.0*arrhythmia) AS pct_with_arrhythmia
, AVG(1.0*alpha_blocker) AS pct_with_alpha_blocker
, AVG(1.0*ihss) AS pct_with_ihss
, AVG(1.0*fibrous_penis) AS pct_with_fibrous_penis
, AVG(1.0*unexplained_fainting_etc) AS pct_with_unexplained_fainting_etc
, AVG(1.0*isosorbide_md) AS pct_with_isosorbide_md
, AVG(1.0*pulm_hypertension) AS pct_with_pulm_hypertension
, AVG(1.0*abnormal_heartbeats) AS pct_with_abnormal_heartbeats
, AVG(1.0*penis_sex_pain) AS pct_with_penis_sex_pain
, AVG(1.0*claudication) AS pct_with_claudication
, AVG(1.0*foreskin_too_tight) AS pct_with_foreskin_too_tight
, AVG(1.0*prost_pelv_surg_rad) AS pct_with_prost_pelv_surg_rad
, AVG(1.0*ms_spine_neural_issue) AS pct_with_ms_spine_neural_issue
, AVG(1.0*nitroglycerin) AS pct_with_nitroglycerin
, AVG(1.0*advised_no_sex) AS pct_with_advised_no_sex
, AVG(1.0*nitrates) AS pct_with_nitrates
, AVG(nitrates) AS pct_with_nitrates
--
FROM
conditions_by_patient
GROUP BY 1
```
------
Before filing an issue we'd appreciate it if you could take a moment to ensure
there isn't already an open issue or pull-request.
-----
If there's an existing issue, please add a :+1: reaction to the description of
the issue. One way we prioritize issues is by the number of :+1: reactions on
their descriptions. Please DO NOT add `+1` or :+1: comments.
### Database support
For requests for new database drivers, please include the name
of the database and the version. Additionally, giving us a sense of how and what
you use it for, and other high-level details about the database, can help us get
a better picture of what would need to done to support it.
### Feature requests and proposals
We're excited to hear how we can make Metabase better. Please add as much detail
as you can on your use case.
### Bugs
If you're filing an issue about a bug please include as much information
as you can including the following.
- Your browser and the version: (e.x. Chrome 52.1, Firefox 48.0, IE 10)
- Your operating system: (e.x. OS X 10, Windows XP, etc)
- Your databases: (e.x. MySQL, Postgres, MongoDB, âŚ)
- Metabase version: (e.x. 0.19.3)
- Metabase hosting environment: (e.x. Mac app, Elastic Beanstalk, Docker, Heroku, Linux/Ubuntu 12)
- Metabase internal database: (e.x. H2 (default), MySQL, Postgres)
- *Repeatable steps to reproduce the issue*
Thanks for being part of the Metabase project!
-------