SQL Join and Query Building

Evernorth runs pharmacy benefits for millions of members, and a care-management analyst needs a report joining member, provider, and prescription-fill data. You are given the schema below and asked to write the query from scratch.

CREATE TABLE members (
    member_id INT PRIMARY KEY,
    member_name VARCHAR(120),
    state CHAR(2),
    plan_type VARCHAR(30)
);

CREATE TABLE providers (
    provider_id INT PRIMARY KEY,
    provider_name VARCHAR(120),
    specialty VARCHAR(60)
);

CREATE TABLE prescriptions (
    prescription_id INT PRIMARY KEY,
    member_id INT REFERENCES members(member_id),
    provider_id INT REFERENCES providers(provider_id),
    drug_name VARCHAR(120),
    fill_date DATE,
    cost NUMERIC(8,2)
);

Write a single SQL query that returns, for every member with more than 3 prescription fills in the last 90 days, the member's name, the names of every provider who prescribed to them in that window, and their total prescription cost, sorted by total cost descending. Explain your join and grouping choices out loud.

Output

1. One row per qualifying member.
2. Columns: member_name, provider_names, and total_prescription_cost.
3. Include only members with more than 3 fills in the last 90 days.
4. Sort by total_prescription_cost descending, then member_name ascending for ties.