zrx-kishore/nlp2sql

1input,instruction,output2"Hi! I need your help in generating an MySQL/ansi sql query.You are sql query generator all you should return is only query I am going to give you the schema associated with a database.3Name of the table is """"test_performance_v3"""".4Schema5Field Name | Field Type | Data Definition6NPI | varchar | 10 digit HCP identifier (Exclude blank and invalid NPIs from any queries)7Claim_ID | varchar | Unique claim identifier8Patient_ID | varchar | Unique patient identifier9Patient_Gender | varchar(8) | Patient Gender (Exclude """"Unknown"""" and blank values from any queries)10Patient_State | varchar | State in which prescription was filled (Exclude """"None"""" and blank values from any queries)11Region | varchar(9) | Region info of state12plan_type_description | varchar | Payor type (COMMERCIAL, MEDICARE, MEDICAID, etc.) in capital letters. (Exclude """"NONE"""" and blank values from any queries)13Plan_Name | varchar | Specific payor name14Drug_name | varchar | Brand or Drug name15Prescribed_Drug_Indication | varchar(2922) | Drug indication (based on prescribed drug)16Prescription_Fill_Year_Month | date | Date when prescription was filled17quantity | double | total quantity of drugs prescribed in each prescription18Total_Patient_Responsibility | double | Out of Pocket expense borne by patient19Total_Payer_Responsibility | double | Expense borne by payor20patient_age | integer | numeric age of patient in years21indication | string | Indications for given patient22Unknown_Flag | varchar(3) | If no disease detected, this would be Yes",How many claims did Kyprolis file in the last three months?,"23 24SELECT COUNT(distinct(claim_ID)) AS num_claims25FROM ""symphony-data"".""test_performance_v3""26WHERE Drug_Name like '%KYPROLIS%'27AND Prescription_Fill_Year_Month >= date((select max(Prescription_Fill_Year_Month)28from ""symphony-data"".""test_performance_v3"") - interval '90' day);"29"Hi! I need your help in generating an MySQL/ansi sql query.You are sql query generator all you should return is only query I am going to give you the schema associated with a database.30Name of the table is """"test_performance_v3"""".31Schema32Field Name | Field Type | Data Definition33NPI | varchar | 10 digit HCP identifier (Exclude blank and invalid NPIs from any queries)34Claim_ID | varchar | Unique claim identifier35Patient_ID | varchar | Unique patient identifier36Patient_Gender | varchar(8) | Patient Gender (Exclude """"Unknown"""" and blank values from any queries)37Patient_State | varchar | State in which prescription was filled (Exclude """"None"""" and blank values from any queries)38Region | varchar(9) | Region info of state39plan_type_description | varchar | Payor type (COMMERCIAL, MEDICARE, MEDICAID, etc.) in capital letters. (Exclude """"NONE"""" and blank values from any queries)40Plan_Name | varchar | Specific payor name41Drug_name | varchar | Brand or Drug name42Prescribed_Drug_Indication | varchar(2922) | Drug indication (based on prescribed drug)43Prescription_Fill_Year_Month | date | Date when prescription was filled44quantity | double | total quantity of drugs prescribed in each prescription45Total_Patient_Responsibility | double | Out of Pocket expense borne by patient46Total_Payer_Responsibility | double | Expense borne by payor47patient_age | integer | numeric age of patient in years48indication | string | Indications for given patient49Unknown_Flag | varchar(3) | If no disease detected, this would be Yes",Which brand had the least claims in MM indication in last 3 months?,"50 51SELECT Drug_Name, COUNT(distinct(claim_ID)) as num_prescriptions52FROM ""symphony-data"".""test_performance_v3""53WHERE indication like '%MM%'54AND Prescription_Fill_Year_Month >= date((select max(Prescription_Fill_Year_Month)55from ""symphony-data"".""test_performance_v3"") - interval '90' day)56GROUP BY Drug_Name57ORDER BY num_prescriptions ASC58LIMIT 1;"59"Hi! I need your help in generating an MySQL/ansi sql query.You are sql query generator all you should return is only query I am going to give you the schema associated with a database.60Name of the table is """"test_performance_v3"""".61Schema62Field Name | Field Type | Data Definition63NPI | varchar | 10 digit HCP identifier (Exclude blank and invalid NPIs from any queries)64Claim_ID | varchar | Unique claim identifier65Patient_ID | varchar | Unique patient identifier66Patient_Gender | varchar(8) | Patient Gender (Exclude """"Unknown"""" and blank values from any queries)67Patient_State | varchar | State in which prescription was filled (Exclude """"None"""" and blank values from any queries)68Region | varchar(9) | Region info of state69plan_type_description | varchar | Payor type (COMMERCIAL, MEDICARE, MEDICAID, etc.) in capital letters. (Exclude """"NONE"""" and blank values from any queries)70Plan_Name | varchar | Specific payor name71Drug_name | varchar | Brand or Drug name72Prescribed_Drug_Indication | varchar(2922) | Drug indication (based on prescribed drug)73Prescription_Fill_Year_Month | date | Date when prescription was filled74quantity | double | total quantity of drugs prescribed in each prescription75Total_Patient_Responsibility | double | Out of Pocket expense borne by patient76Total_Payer_Responsibility | double | Expense borne by payor77patient_age | integer | numeric age of patient in years78indication | string | Indications for given patient79Unknown_Flag | varchar(3) | If no disease detected, this would be Yes",Give me the market share by drug for the bottom 5 drugs for MM indication over the last 9 months,"80 81WITH82top_5_drugs AS (83SELECT Drug_Name, count (distinct(claim_id)) as prescription_count84FROM ""symphony-data"".""test_performance_v3""85WHERE indication like '%MM%'86AND Prescription_Fill_Year_Month >= date((select max(Prescription_Fill_Year_Month)87from ""symphony-data"".""test_performance_v3"") - interval '9' month)88GROUP BY Drug_Name89order by prescription_count asc90limit 591),92total_prescriptions AS (93SELECT COUNT(distinct(claim_ID)) AS total_prescriptions94FROM ""symphony-data"".""test_performance_v3""95WHERE indication like '%MM%'96AND Prescription_Fill_Year_Month >= date((select max(Prescription_Fill_Year_Month)97from ""symphony-data"".""test_performance_v3"") - interval '9' month)98)99SELECT 100top_5_drugs.Drug_Name, 101ROUND(100.0 * top_5_drugs.prescription_count / total_prescriptions.total_prescriptions, 2) as market_share 102FROM top_5_drugs, total_prescriptions;"103"Hi! I need your help in generating an MySQL/ansi sql query.You are sql query generator all you should return is only query I am going to give you the schema associated with a database.104Name of the table is """"test_performance_v3"""".105Schema106Field Name | Field Type | Data Definition107NPI | varchar | 10 digit HCP identifier (Exclude blank and invalid NPIs from any queries)108Claim_ID | varchar | Unique claim identifier109Patient_ID | varchar | Unique patient identifier110Patient_Gender | varchar(8) | Patient Gender (Exclude """"Unknown"""" and blank values from any queries)111Patient_State | varchar | State in which prescription was filled (Exclude """"None"""" and blank values from any queries)112Region | varchar(9) | Region info of state113plan_type_description | varchar | Payor type (COMMERCIAL, MEDICARE, MEDICAID, etc.) in capital letters. (Exclude """"NONE"""" and blank values from any queries)114Plan_Name | varchar | Specific payor name115Drug_name | varchar | Brand or Drug name116Prescribed_Drug_Indication | varchar(2922) | Drug indication (based on prescribed drug)117Prescription_Fill_Year_Month | date | Date when prescription was filled118quantity | double | total quantity of drugs prescribed in each prescription119Total_Patient_Responsibility | double | Out of Pocket expense borne by patient120Total_Payer_Responsibility | double | Expense borne by payor121patient_age | integer | numeric age of patient in years122indication | string | Indications for given patient123Unknown_Flag | varchar(3) | If no disease detected, this would be Yes",Which brand had the highest claims in MM indication in last 9 months?,"124 125SELECT Drug_Name, COUNT(distinct(claim_ID)) as num_prescriptions126FROM ""symphony-data"".""test_performance_v3""127WHERE indication like '%MM%'128AND Prescription_Fill_Year_Month >= date((select max(Prescription_Fill_Year_Month)129from ""symphony-data"".""test_performance_v3"") - interval '9' month)130GROUP BY Drug_Name131ORDER BY num_prescriptions DESC132LIMIT 1;"133"Hi! I need your help in generating an MySQL/ansi sql query.You are sql query generator all you should return is only query I am going to give you the schema associated with a database.134Name of the table is """"test_performance_v3"""".135Schema136Field Name | Field Type | Data Definition137NPI | varchar | 10 digit HCP identifier (Exclude blank and invalid NPIs from any queries)138Claim_ID | varchar | Unique claim identifier139Patient_ID | varchar | Unique patient identifier140Patient_Gender | varchar(8) | Patient Gender (Exclude """"Unknown"""" and blank values from any queries)141Patient_State | varchar | State in which prescription was filled (Exclude """"None"""" and blank values from any queries)142Region | varchar(9) | Region info of state143plan_type_description | varchar | Payor type (COMMERCIAL, MEDICARE, MEDICAID, etc.) in capital letters. (Exclude """"NONE"""" and blank values from any queries)144Plan_Name | varchar | Specific payor name145Drug_name | varchar | Brand or Drug name146Prescribed_Drug_Indication | varchar(2922) | Drug indication (based on prescribed drug)147Prescription_Fill_Year_Month | date | Date when prescription was filled148quantity | double | total quantity of drugs prescribed in each prescription149Total_Patient_Responsibility | double | Out of Pocket expense borne by patient150Total_Payer_Responsibility | double | Expense borne by payor151patient_age | integer | numeric age of patient in years152indication | string | Indications for given patient153Unknown_Flag | varchar(3) | If no disease detected, this would be Yes",What was the market share for Kyprolis for years 2021 and 2018 for MM indication?,"154 155WITH156kyprolis_2021 AS (157SELECT COUNT(distinct(claim_ID)) AS num_prescriptions158FROM ""symphony-data"".""test_performance_v3""159WHERE Drug_Name like '%KYPROLIS%'160AND indication like '%MM%'161AND Prescription_Fill_Year_Month >= date('2021-01-01')162AND Prescription_Fill_Year_Month <= date('2021-12-31')163),164kyprolis_2018 AS (165SELECT COUNT(distinct(claim_ID)) AS num_prescriptions166FROM ""symphony-data"".""test_performance_v3""167WHERE Drug_Name like '%KYPROLIS%'168AND indication like '%MM%'169AND Prescription_Fill_Year_Month >= date('2018-01-01')170AND Prescription_Fill_Year_Month <= date('2018-12-31')171),172total_2021 AS (173SELECT COUNT(distinct(claim_ID)) AS num_prescriptions174FROM ""symphony-data"".""test_performance_v3""175WHERE indication like '%MM%'176AND Prescription_Fill_Year_Month >= date('2021-01-01')177AND Prescription_Fill_Year_Month <= date('2021-12-31')178),179total_2018 AS (180SELECT COUNT(distinct(claim_ID)) AS num_prescriptions181FROM ""symphony-data"".""test_performance_v3""182WHERE indication like '%MM%'183AND Prescription_Fill_Year_Month >= date('2018-01-01')184AND Prescription_Fill_Year_Month <= date('2018-12-31')185)186SELECT 187ROUND(100.0 * kyprolis_2021.num_prescriptions / total_2021.num_prescriptions, 2) AS kyprolis_2021_market_share,188ROUND(100.0 * kyprolis_2018.num_prescriptions / total_2018.num_prescriptions, 2) AS kyprolis_2018_market_share189FROM kyprolis_2021, kyprolis_2018, total_2021, total_2018;"190"Hi! I need your help in generating an MySQL/ansi sql query.You are sql query generator all you should return is only query I am going to give you the schema associated with a database.191Name of the table is """"test_performance_v3"""".192Schema193Field Name | Field Type | Data Definition194NPI | varchar | 10 digit HCP identifier (Exclude blank and invalid NPIs from any queries)195Claim_ID | varchar | Unique claim identifier196Patient_ID | varchar | Unique patient identifier197Patient_Gender | varchar(8) | Patient Gender (Exclude """"Unknown"""" and blank values from any queries)198Patient_State | varchar | State in which prescription was filled (Exclude """"None"""" and blank values from any queries)199Region | varchar(9) | Region info of state200plan_type_description | varchar | Payor type (COMMERCIAL, MEDICARE, MEDICAID, etc.) in capital letters. (Exclude """"NONE"""" and blank values from any queries)201Plan_Name | varchar | Specific payor name202Drug_name | varchar | Brand or Drug name203Prescribed_Drug_Indication | varchar(2922) | Drug indication (based on prescribed drug)204Prescription_Fill_Year_Month | date | Date when prescription was filled205quantity | double | total quantity of drugs prescribed in each prescription206Total_Patient_Responsibility | double | Out of Pocket expense borne by patient207Total_Payer_Responsibility | double | Expense borne by payor208patient_age | integer | numeric age of patient in years209indication | string | Indications for given patient210Unknown_Flag | varchar(3) | If no disease detected, this would be Yes","Over the past 9 months, which state in the US has seen a higher number of claims for Kyprolis in the MM indication compared to its competitors?","211 212WITH kyprolis_claims AS (213SELECT Patient_State, COUNT(distinct(claim_ID)) AS num_claims214FROM ""symphony-data"".""test_performance_v3""215WHERE Drug_Name like '%KYPROLIS%'216AND indication like '%MM%'217AND Prescription_Fill_Year_Month >= date((select max(Prescription_Fill_Year_Month)218from ""symphony-data"".""test_performance_v3"") - interval '9' month)219GROUP BY Patient_State220),221competitor_claims AS (222SELECT Patient_State, COUNT(distinct(claim_ID)) AS num_claims223FROM ""symphony-data"".""test_performance_v3""224WHERE Drug_Name not like '%KYPROLIS%'225AND indication like '%MM%'226AND Prescription_Fill_Year_Month >= date((select max(Prescription_Fill_Year_Month)227from ""symphony-data"".""test_performance_v3"") - interval '9' month)228GROUP BY Patient_State229)230SELECT kyprolis_claims.Patient_State231FROM kyprolis_claims, competitor_claims232WHERE kyprolis_claims.num_claims > competitor_claims.num_claims;"233"Hi! I need your help in generating an MySQL/ansi sql query.You are sql query generator all you should return is only query I am going to give you the schema associated with a database.234Name of the table is """"test_performance_v3"""".235Schema236Field Name | Field Type | Data Definition237NPI | varchar | 10 digit HCP identifier (Exclude blank and invalid NPIs from any queries)238Claim_ID | varchar | Unique claim identifier239Patient_ID | varchar | Unique patient identifier240Patient_Gender | varchar(8) | Patient Gender (Exclude """"Unknown"""" and blank values from any queries)241Patient_State | varchar | State in which prescription was filled (Exclude """"None"""" and blank values from any queries)242Region | varchar(9) | Region info of state243plan_type_description | varchar | Payor type (COMMERCIAL, MEDICARE, MEDICAID, etc.) in capital letters. (Exclude """"NONE"""" and blank values from any queries)244Plan_Name | varchar | Specific payor name245Drug_name | varchar | Brand or Drug name246Prescribed_Drug_Indication | varchar(2922) | Drug indication (based on prescribed drug)247Prescription_Fill_Year_Month | date | Date when prescription was filled248quantity | double | total quantity of drugs prescribed in each prescription249Total_Patient_Responsibility | double | Out of Pocket expense borne by patient250Total_Payer_Responsibility | double | Expense borne by payor251patient_age | integer | numeric age of patient in years252indication | string | Indications for given patient253Unknown_Flag | varchar(3) | If no disease detected, this would be Yes",What is the relative growth of Kyprolis in last 5 months compared to previous year?,"254 255WITH256numerator AS (257select count(Distinct(claim_ID)) as total_claims from ""symphony-data"".""test_performance_v3"" where Prescription_Fill_Year_Month >= date((select max(Prescription_Fill_Year_Month)258from ""symphony-data"".""test_performance_v3"") - interval '5' month) and Drug_Name like '%KYPROLIS%'259),260denominator AS(261select count(distinct(claim_ID)) as total_claims from ""symphony-data"".""test_performance_v3"" where Prescription_Fill_Year_Month >= date((select max(Prescription_Fill_Year_Month)262from ""symphony-data"".""test_performance_v3"") - interval '1' year - interval '5' month) and Prescription_Fill_Year_Month <= date((select max(Prescription_Fill_Year_Month)263from ""symphony-data"".""test_performance_v3"") - interval '1' year) and Drug_Name like '%KYPROLIS%'264)265SELECT (cast(numerator.total_claims as double) / (denominator.total_claims)-1)*100 as relative_growth from numerator, denominator;"266"Hi! I need your help in generating an MySQL/ansi sql query.You are sql query generator all you should return is only query I am going to give you the schema associated with a database.267Name of the table is """"test_performance_v3"""".268Schema269Field Name | Field Type | Data Definition270NPI | varchar | 10 digit HCP identifier (Exclude blank and invalid NPIs from any queries)271Claim_ID | varchar | Unique claim identifier272Patient_ID | varchar | Unique patient identifier273Patient_Gender | varchar(8) | Patient Gender (Exclude """"Unknown"""" and blank values from any queries)274Patient_State | varchar | State in which prescription was filled (Exclude """"None"""" and blank values from any queries)275Region | varchar(9) | Region info of state276plan_type_description | varchar | Payor type (COMMERCIAL, MEDICARE, MEDICAID, etc.) in capital letters. (Exclude """"NONE"""" and blank values from any queries)277Plan_Name | varchar | Specific payor name278Drug_name | varchar | Brand or Drug name279Prescribed_Drug_Indication | varchar(2922) | Drug indication (based on prescribed drug)280Prescription_Fill_Year_Month | date | Date when prescription was filled281quantity | double | total quantity of drugs prescribed in each prescription282Total_Patient_Responsibility | double | Out of Pocket expense borne by patient283Total_Payer_Responsibility | double | Expense borne by payor284patient_age | integer | numeric age of patient in years285indication | string | Indications for given patient286Unknown_Flag | varchar(3) | If no disease detected, this would be Yes",What is the market share of Kyprolis in MM indication for patients above 40 years?,"287 288WITH num AS (289SELECT COUNT(DISTINCT(Claim_ID)) AS num_data 290FROM ""symphony-data"".""test_performance_v3"" 291WHERE Drug_Name LIKE '%KYPROLIS%' 292AND indication LIKE '%MM%' 293AND patient_age > 40 294AND Prescription_Fill_Year_Month >= date((select max(Prescription_Fill_Year_Month) 295from ""symphony-data"".""test_performance_v3"") - interval '90' day)296),297den AS (298SELECT COUNT(DISTINCT(Claim_ID)) AS den_data 299FROM ""symphony-data"".""test_performance_v3"" 300WHERE indication LIKE '%MM%' 301AND patient_age > 40 302AND Prescription_Fill_Year_Month >= date((select max(Prescription_Fill_Year_Month) 303from ""symphony-data"".""test_performance_v3"") - interval '90' day)304)305SELECT ROUND(100.0 * num_data / den_data, 2) AS market_share 306FROM num, den;"307"Hi! I need your help in generating an MySQL/ansi sql query.You are sql query generator all you should return is only query I am going to give you the schema associated with a database.308Name of the table is """"test_performance_v3"""".309Schema310Field Name | Field Type | Data Definition311NPI | varchar | 10 digit HCP identifier (Exclude blank and invalid NPIs from any queries)312Claim_ID | varchar | Unique claim identifier313Patient_ID | varchar | Unique patient identifier314Patient_Gender | varchar(8) | Patient Gender (Exclude """"Unknown"""" and blank values from any queries)315Patient_State | varchar | State in which prescription was filled (Exclude """"None"""" and blank values from any queries)316Region | varchar(9) | Region info of state317plan_type_description | varchar | Payor type (COMMERCIAL, MEDICARE, MEDICAID, etc.) in capital letters. (Exclude """"NONE"""" and blank values from any queries)318Plan_Name | varchar | Specific payor name319Drug_name | varchar | Brand or Drug name320Prescribed_Drug_Indication | varchar(2922) | Drug indication (based on prescribed drug)321Prescription_Fill_Year_Month | date | Date when prescription was filled322quantity | double | total quantity of drugs prescribed in each prescription323Total_Patient_Responsibility | double | Out of Pocket expense borne by patient324Total_Payer_Responsibility | double | Expense borne by payor325patient_age | integer | numeric age of patient in years326indication | string | Indications for given patient327Unknown_Flag | varchar(3) | If no disease detected, this would be Yes",Which drug had lowest claims for patients above 60 in MM indication over the last 1 year?,"328 329SELECT Drug_Name, COUNT(distinct(claim_ID)) as num_claims330FROM ""symphony-data"".""test_performance_v3""331WHERE indication like '%MM%'332AND patient_age > 60333AND Prescription_Fill_Year_Month >= date((select max(Prescription_Fill_Year_Month)334from ""symphony-data"".""test_performance_v3"") - interval '1' year)335GROUP BY Drug_Name336ORDER BY num_claims ASC337LIMIT 1;"338"Hi! I need your help in generating an MySQL/ansi sql query.You are sql query generator all you should return is only query I am going to give you the schema associated with a database.339Name of the table is """"test_performance_v3"""".340Schema341Field Name | Field Type | Data Definition342NPI | varchar | 10 digit HCP identifier (Exclude blank and invalid NPIs from any queries)343Claim_ID | varchar | Unique claim identifier344Patient_ID | varchar | Unique patient identifier345Patient_Gender | varchar(8) | Patient Gender (Exclude """"Unknown"""" and blank values from any queries)346Patient_State | varchar | State in which prescription was filled (Exclude """"None"""" and blank values from any queries)347Region | varchar(9) | Region info of state348plan_type_description | varchar | Payor type (COMMERCIAL, MEDICARE, MEDICAID, etc.) in capital letters. (Exclude """"NONE"""" and blank values from any queries)349Plan_Name | varchar | Specific payor name350Drug_name | varchar | Brand or Drug name351Prescribed_Drug_Indication | varchar(2922) | Drug indication (based on prescribed drug)352Prescription_Fill_Year_Month | date | Date when prescription was filled353quantity | double | total quantity of drugs prescribed in each prescription354Total_Patient_Responsibility | double | Out of Pocket expense borne by patient355Total_Payer_Responsibility | double | Expense borne by payor356patient_age | integer | numeric age of patient in years357indication | string | Indications for given patient358Unknown_Flag | varchar(3) | If no disease detected, this would be Yes",In which state did Kyprolis have the highest number of prescriptions for the itp indication?,"359 360SELECT Patient_State, COUNT(distinct(Claim_ID)) as num_prescriptions361FROM ""symphony-data"".""test_performance_v3""362WHERE Drug_Name like '%KYPROLIS%'363AND indication like '%ITP%'364GROUP BY Patient_State365ORDER BY num_prescriptions DESC366LIMIT 1;"367"Hi! I need your help in generating an MySQL/ansi sql query.You are sql query generator all you should return is only query I am going to give you the schema associated with a database.368Name of the table is """"test_performance_v3"""".369Schema370Field Name | Field Type | Data Definition371NPI | varchar | 10 digit HCP identifier (Exclude blank and invalid NPIs from any queries)372Claim_ID | varchar | Unique claim identifier373Patient_ID | varchar | Unique patient identifier374Patient_Gender | varchar(8) | Patient Gender (Exclude """"Unknown"""" and blank values from any queries)375Patient_State | varchar | State in which prescription was filled (Exclude """"None"""" and blank values from any queries)376Region | varchar(9) | Region info of state377plan_type_description | varchar | Payor type (COMMERCIAL, MEDICARE, MEDICAID, etc.) in capital letters. (Exclude """"NONE"""" and blank values from any queries)378Plan_Name | varchar | Specific payor name379Drug_name | varchar | Brand or Drug name380Prescribed_Drug_Indication | varchar(2922) | Drug indication (based on prescribed drug)381Prescription_Fill_Year_Month | date | Date when prescription was filled382quantity | double | total quantity of drugs prescribed in each prescription383Total_Patient_Responsibility | double | Out of Pocket expense borne by patient384Total_Payer_Responsibility | double | Expense borne by payor385patient_age | integer | numeric age of patient in years386indication | string | Indications for given patient387Unknown_Flag | varchar(3) | If no disease detected, this would be Yes","Over the past 9 months, which state in the US had a lowest number of claims for Kyprolis in the cml indication compared to its competitors?","388 389WITH kyprolis_prescriptions AS (390SELECT COUNT(distinct(claim_ID)) AS num_prescriptions, Patient_State391FROM ""symphony-data"".""test_performance_v3""392WHERE Drug_Name like '%KYPROLIS%'393AND indication like '%CML%'394AND Prescription_Fill_Year_Month >= date((select max(Prescription_Fill_Year_Month)395from ""symphony-data"".""test_performance_v3"") - interval '9' month)396GROUP BY Patient_State397),398competitor_prescriptions AS (399SELECT COUNT(distinct(claim_ID)) AS num_prescriptions, Patient_State400FROM ""symphony-data"".""test_performance_v3""401WHERE Drug_Name not like '%KYPROLIS%'402AND indication like '%CML%'403AND Prescription_Fill_Year_Month >= date((select max(Prescription_Fill_Year_Month)404from ""symphony-data"".""test_performance_v3"") - interval '9' month)405GROUP BY Patient_State406)407SELECT kyprolis_prescriptions.Patient_State, kyprolis_prescriptions.num_prescriptions/competitor_prescriptions.num_prescriptions as ratio408FROM kyprolis_prescriptions, competitor_prescriptions409WHERE kyprolis_prescriptions.Patient_State = competitor_prescriptions.Patient_State410ORDER BY ratio ASC411LIMIT 1;"412"Hi! I need your help in generating an MySQL/ansi sql query.You are sql query generator all you should return is only query I am going to give you the schema associated with a database.413Name of the table is """"test_performance_v3"""".414Schema415Field Name | Field Type | Data Definition416NPI | varchar | 10 digit HCP identifier (Exclude blank and invalid NPIs from any queries)417Claim_ID | varchar | Unique claim identifier418Patient_ID | varchar | Unique patient identifier419Patient_Gender | varchar(8) | Patient Gender (Exclude """"Unknown"""" and blank values from any queries)420Patient_State | varchar | State in which prescription was filled (Exclude """"None"""" and blank values from any queries)421Region | varchar(9) | Region info of state422plan_type_description | varchar | Payor type (COMMERCIAL, MEDICARE, MEDICAID, etc.) in capital letters. (Exclude """"NONE"""" and blank values from any queries)423Plan_Name | varchar | Specific payor name424Drug_name | varchar | Brand or Drug name425Prescribed_Drug_Indication | varchar(2922) | Drug indication (based on prescribed drug)426Prescription_Fill_Year_Month | date | Date when prescription was filled427quantity | double | total quantity of drugs prescribed in each prescription428Total_Patient_Responsibility | double | Out of Pocket expense borne by patient429Total_Payer_Responsibility | double | Expense borne by payor430patient_age | integer | numeric age of patient in years431indication | string | Indications for given patient432Unknown_Flag | varchar(3) | If no disease detected, this would be Yes",What is the most common payer type for Kyprolis in the past 9 months?,"433 434SELECT plan_type_description, COUNT(distinct(claim_ID)) as num_prescriptions435FROM ""symphony-data"".""test_performance_v3""436WHERE Drug_Name like '%KYPROLIS%'437AND Prescription_Fill_Year_Month >= date((select max(Prescription_Fill_Year_Month)438from ""symphony-data"".""test_performance_v3"") - interval '9' month)439AND plan_type_description NOT IN ('NONE', '')440GROUP BY plan_type_description441ORDER BY num_prescriptions DESC442LIMIT 1;"443"Hi! I need your help in generating an MySQL/ansi sql query.You are sql query generator all you should return is only query I am going to give you the schema associated with a database.444Name of the table is """"test_performance_v3"""".445Schema446Field Name | Field Type | Data Definition447NPI | varchar | 10 digit HCP identifier (Exclude blank and invalid NPIs from any queries)448Claim_ID | varchar | Unique claim identifier449Patient_ID | varchar | Unique patient identifier450Patient_Gender | varchar(8) | Patient Gender (Exclude """"Unknown"""" and blank values from any queries)451Patient_State | varchar | State in which prescription was filled (Exclude """"None"""" and blank values from any queries)452Region | varchar(9) | Region info of state453plan_type_description | varchar | Payor type (COMMERCIAL, MEDICARE, MEDICAID, etc.) in capital letters. (Exclude """"NONE"""" and blank values from any queries)454Plan_Name | varchar | Specific payor name455Drug_name | varchar | Brand or Drug name456Prescribed_Drug_Indication | varchar(2922) | Drug indication (based on prescribed drug)457Prescription_Fill_Year_Month | date | Date when prescription was filled458quantity | double | total quantity of drugs prescribed in each prescription459Total_Patient_Responsibility | double | Out of Pocket expense borne by patient460Total_Payer_Responsibility | double | Expense borne by payor461patient_age | integer | numeric age of patient in years462indication | string | Indications for given patient463Unknown_Flag | varchar(3) | If no disease detected, this would be Yes",What is the average out-of-pocket payment for Kyprolis in the state of New York (NY) in the past  month?,"464 465SELECT AVG(Total_Patient_Responsibility) as avg_patient_pay466FROM ""symphony-data"".""test_performance_v3""467WHERE Drug_Name like '%KYPROLIS%'468AND Patient_State = 'NY'469AND Prescription_Fill_Year_Month >= date((select max(Prescription_Fill_Year_Month)470from ""symphony-data"".""test_performance_v3"") - interval '1' month);"471"Hi! I need your help in generating an MySQL/ansi sql query.You are sql query generator all you should return is only query I am going to give you the schema associated with a database.472Name of the table is """"test_performance_v3"""".473Schema474Field Name | Field Type | Data Definition475NPI | varchar | 10 digit HCP identifier (Exclude blank and invalid NPIs from any queries)476Claim_ID | varchar | Unique claim identifier477Patient_ID | varchar | Unique patient identifier478Patient_Gender | varchar(8) | Patient Gender (Exclude """"Unknown"""" and blank values from any queries)479Patient_State | varchar | State in which prescription was filled (Exclude """"None"""" and blank values from any queries)480Region | varchar(9) | Region info of state481plan_type_description | varchar | Payor type (COMMERCIAL, MEDICARE, MEDICAID, etc.) in capital letters. (Exclude """"NONE"""" and blank values from any queries)482Plan_Name | varchar | Specific payor name483Drug_name | varchar | Brand or Drug name484Prescribed_Drug_Indication | varchar(2922) | Drug indication (based on prescribed drug)485Prescription_Fill_Year_Month | date | Date when prescription was filled486quantity | double | total quantity of drugs prescribed in each prescription487Total_Patient_Responsibility | double | Out of Pocket expense borne by patient488Total_Payer_Responsibility | double | Expense borne by payor489patient_age | integer | numeric age of patient in years490indication | string | Indications for given patient491Unknown_Flag | varchar(3) | If no disease detected, this would be Yes",What was the market share of the last 5 drugs prescribed for the cml indication over the past 6 months?,"492 493WITH494top_5_drugs AS (495SELECT Drug_Name, count (distinct(claim_id)) as prescription_count496FROM ""symphony-data"".""test_performance_v3""497WHERE indication like '%CML%'498AND Prescription_Fill_Year_Month >= date((select max(Prescription_Fill_Year_Month)499from ""symphony-data"".""test_performance_v3"") - interval '6' month)500GROUP BY Drug_Name501order by prescription_count desc502limit 5503),504total_prescriptions AS (505SELECT COUNT(distinct(claim_ID)) AS total_prescriptions506FROM ""symphony-data"".""test_performance_v3""507WHERE indication like '%CML%'508AND Prescription_Fill_Year_Month >= date((select max(Prescription_Fill_Year_Month)509from ""symphony-data"".""test_performance_v3"") - interval '6' month)510)511SELECT 512top_5_drugs.Drug_Name, 513ROUND(100.0 * top_5_drugs.prescription_count / total_prescriptions.total_prescriptions, 2) as market_share514FROM top_5_drugs, total_prescriptions;"