Verified References on Each Order

Problem

Three DataFrames describe a platform's members, the reference contacts they listed, and the orders they placed.

`member` (`member.csv`): `member_id`, `member_name`, `email` — one registered member per row.

`reference` (`reference.csv`): `member_id`, `ref_name`, `ref_email` — each row is a reference contact a member listed. A reference is **verified** when its `ref_email` matches the `email` of some registered member.

`order` (`order.csv`): `order_id`, `total`, `member_id` — one order placed by a member.

For each order, report `order_id`, the ordering member's `member_name`, the order `total`, the number of references that member listed (`refs_cnt`), and how many of those references are verified (`verified_cnt`). Return the rows ordered by `order_id`.

**Example**

If a member listed 3 references and 2 of their `ref_email`s belong to registered members, that order shows `refs_cnt = 3` and `verified_cnt = 2`.

Input data

Example rows — the live problem includes the full dataset.

Expected output

Your answer should return 2 rows with the columns order_id, member_name, total, refs_cnt, verified_cnt.

Starter code (Pandas (Python))

import pandas as pd

def verified_references_per_order(member: pd.DataFrame, reference: pd.DataFrame, order: pd.DataFrame) -> pd.DataFrame:
    # Your code here
    return member

Related Pandas questions

• 7-Day Rolling Signup AverageMedium
• Accounts Streaming From Two Devices at OnceMedium
• Agent Resolution RateMedium
• Agents With a Dominant Contact ChannelMedium
• Airbnb Popularity Bucket PricingMedium
• Amazon Unique Shipments per MonthMedium