Prescription Value per Patient

Return each patient name and the total value (qty * price) of their prescriptions. Order by total value descending, then name.

Table schema

Patients(patient_id INT PRIMARY KEY, name VARCHAR(30))
Drugs(drug_id INT PRIMARY KEY, name VARCHAR(30), price INT)
Prescriptions(rx_id INT PRIMARY KEY, patient_id INT, drug_id INT, qty INT, rx_date DATE)

Sample data

Patients
patient_id | name
1 | Asha
2 | Ben
3 | Chitra

Drugs
drug_id | name | price
1 | Paracetamol | 5
2 | Amoxicillin | 20
3 | Insulin | 50
4 | Aspirin | 4

Prescriptions
rx_id | patient_id | drug_id | qty | rx_date
1 | 1 | 1 | 10 | 2026-01-05
2 | 1 | 2 | 2 | 2026-01-20
3 | 2 | 3 | 1 | 2026-01-12
4 | 2 | 1 | 6 | 2026-02-03
5 | 1 | 1 | 4 | 2026-02-10
6 | 3 | 3 | 2 | 2026-02-14