Total Prescriptions Per Member | Dataford Interview Questions

Given a table of prescription transactions and a table of members, write a query to find the total prescriptions per member for the last 30 days.

Return every member, including members with no prescriptions during the period. Treat the last 30 days as transactions from the current date through 30 days earlier, inclusive.

Output

One row per member with columns member_id, member_name, and total_prescriptions
Include members whose total is zero
Order by member_id ascending

Schema

members

Column	Type	Description
member_idPK	INT	Unique Optum Rx member identifier
member_name	VARCHAR(100)	Member's full name

prescription_transactions

Column	Type	Description
transaction_idPK	INT	Unique prescription transaction identifier
member_id	INT	Member associated with the prescription transaction
transaction_date	DATE	Date the prescription transaction occurred

Your question is Total Prescriptions Per Member. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem