Invoice Contact Trust Coverage Report

Medium

A commerce platform stores customer identity records, customer-maintained contact lists, and invoice transactions in three relational tables. Table: customers customer_id: unique customer identifier customer_name: display name of the customer email: primary customer email Table: contacts user_id: customer who owns this contact entry contact_name: human-readable contact label contact_email: email recorded for that contact each (user_id, contact_email) pair is unique Table: invoices invoice_id: unique invoice identifier price: invoice amount user_id: customer linked to the invoice Task: For every invoice, compute a contact trust coverage summary. For each invoice row, return: invoice_id customer_name price contacts_cnt: number of contact rows owned by that invoice user trusted_contacts_cnt: number of those contacts whose contact_email appears in customers.email Output requirements: Return one row per invoice. Order rows by invoice_id ascending. Return columns exactly as listed above. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

customers:
| customer_id | customer_name | email               |
|-------------|---------------|---------------------|
| 1           | Alice         | alice@shop.com      |
| 2           | Bob           | bob@shop.com        |
| 13          | Joon          | joon@shop.com       |
| 6           | Alex          | alex@shop.com       |

contacts:
| user_id | contact_name | contact_email      |
|---------|--------------|--------------------|
| 1       | Bob          | bob@shop.com       |
| 1       | Joon         | joon@shop.com      |
| 1       | Maya         | maya@extmail.com   |
| 2       | Omar         | omar@extmail.com   |
| 2       | Meir         | meir@extmail.com   |
| 6       | Alice        | alice@shop.com     |

invoices:
| invoice_id | price | user_id |
|------------|-------|---------|
| 77         | 100   | 1       |
| 88         | 200   | 1       |
| 99         | 300   | 2       |
| 66         | 400   | 2       |
| 55         | 500   | 13      |
| 44         | 60    | 6       |

Output

[
  {"invoice_id":44,"customer_name":"Alex","price":60,"contacts_cnt":1,"trusted_contacts_cnt":1},
  {"invoice_id":55,"customer_name":"Joon","price":500,"contacts_cnt":0,"trusted_contacts_cnt":0},
  {"invoice_id":66,"customer_name":"Bob","price":400,"contacts_cnt":2,"trusted_contacts_cnt":0},
  {"invoice_id":77,"customer_name":"Alice","price":100,"contacts_cnt":3,"trusted_contacts_cnt":2},
  {"invoice_id":88,"customer_name":"Alice","price":200,"contacts_cnt":3,"trusted_contacts_cnt":2},
  {"invoice_id":99,"customer_name":"Bob","price":300,"contacts_cnt":2,"trusted_contacts_cnt":0}
]

Explanation

Alice has 3 contacts and 2 trusted contacts. Bob has 2 contacts and none are trusted. Alex has 1 trusted contact. Joon has no contacts.

Example 2

Input

customers:
| customer_id | customer_name | email            |
|-------------|---------------|------------------|
| 10          | Nora          | nora@corp.io     |
| 11          | Luis          | luis@corp.io     |

contacts:
| user_id | contact_name | contact_email      |
|---------|--------------|--------------------|
| 10      | Vendor A     | billing@vendor.io  |

invoices:
| invoice_id | price | user_id |
|------------|-------|---------|
| 1          | 80    | 10      |
| 2          | 120   | 11      |

Output

[
  {"invoice_id":1,"customer_name":"Nora","price":80,"contacts_cnt":1,"trusted_contacts_cnt":0},
  {"invoice_id":2,"customer_name":"Luis","price":120,"contacts_cnt":0,"trusted_contacts_cnt":0}
]

Explanation

Customer 10 has one untrusted contact. Customer 11 has no contacts.

Example 3

Input

customers:
| customer_id | customer_name | email            |
|-------------|---------------|------------------|
| 1           | Priya         | priya@data.ai    |
| 2           | Raj           | raj@data.ai      |
| 3           | Mina          | mina@data.ai     |

contacts:
| user_id | contact_name | contact_email   |
|---------|--------------|-----------------|
| 1       | Raj          | raj@data.ai     |
| 1       | Raj Caps     | RAJ@data.ai     |
| 1       | Mina         | mina@data.ai    |

invoices:
| invoice_id | price | user_id |
|------------|-------|---------|
| 7          | 900   | 1       |
| 8          | 300   | 1       |

Output

[
  {"invoice_id":7,"customer_name":"Priya","price":900,"contacts_cnt":3,"trusted_contacts_cnt":2},
  {"invoice_id":8,"customer_name":"Priya","price":300,"contacts_cnt":3,"trusted_contacts_cnt":2}
]

Explanation

Email matching is exact in this setup, so RAJ@data.ai does not match raj@data.ai.

Constraints

0 <= number of rows in customers <= 200000
0 <= number of rows in contacts <= 200000
0 <= number of rows in invoices <= 200000
customer_id is unique in customers
email is unique in customers
(user_id, contact_email) is unique in contacts
invoice_id is unique in invoices
invoices.user_id references an existing customers.customer_id
contacts.user_id references an existing customers.customer_id
1 <= customer_id, user_id, invoice_id, price <= 1000000000
customer_name, email, contact_name, and contact_email are non-empty strings
Return exactly: invoice_id, customer_name, price, contacts_cnt, trusted_contacts_cnt
Order by invoice_id ascending
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with the required output columns

Preparing your editor. You can read the problem while it loads.

Invoice Contact Trust Coverage Report

Medium

A commerce platform stores customer identity records, customer-maintained contact lists, and invoice transactions in three relational tables. Table: customers customer_id: unique customer identifier customer_name: display name of the customer email: primary customer email Table: contacts user_id: customer who owns this contact entry contact_name: human-readable contact label contact_email: email recorded for that contact each (user_id, contact_email) pair is unique Table: invoices invoice_id: unique invoice identifier price: invoice amount user_id: customer linked to the invoice Task: For every invoice, compute a contact trust coverage summary. For each invoice row, return: invoice_id customer_name price contacts_cnt: number of contact rows owned by that invoice user trusted_contacts_cnt: number of those contacts whose contact_email appears in customers.email Output requirements: Return one row per invoice. Order rows by invoice_id ascending. Return columns exactly as listed above. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

customers:
| customer_id | customer_name | email               |
|-------------|---------------|---------------------|
| 1           | Alice         | alice@shop.com      |
| 2           | Bob           | bob@shop.com        |
| 13          | Joon          | joon@shop.com       |
| 6           | Alex          | alex@shop.com       |

contacts:
| user_id | contact_name | contact_email      |
|---------|--------------|--------------------|
| 1       | Bob          | bob@shop.com       |
| 1       | Joon         | joon@shop.com      |
| 1       | Maya         | maya@extmail.com   |
| 2       | Omar         | omar@extmail.com   |
| 2       | Meir         | meir@extmail.com   |
| 6       | Alice        | alice@shop.com     |

invoices:
| invoice_id | price | user_id |
|------------|-------|---------|
| 77         | 100   | 1       |
| 88         | 200   | 1       |
| 99         | 300   | 2       |
| 66         | 400   | 2       |
| 55         | 500   | 13      |
| 44         | 60    | 6       |

Output

[
  {"invoice_id":44,"customer_name":"Alex","price":60,"contacts_cnt":1,"trusted_contacts_cnt":1},
  {"invoice_id":55,"customer_name":"Joon","price":500,"contacts_cnt":0,"trusted_contacts_cnt":0},
  {"invoice_id":66,"customer_name":"Bob","price":400,"contacts_cnt":2,"trusted_contacts_cnt":0},
  {"invoice_id":77,"customer_name":"Alice","price":100,"contacts_cnt":3,"trusted_contacts_cnt":2},
  {"invoice_id":88,"customer_name":"Alice","price":200,"contacts_cnt":3,"trusted_contacts_cnt":2},
  {"invoice_id":99,"customer_name":"Bob","price":300,"contacts_cnt":2,"trusted_contacts_cnt":0}
]

Explanation

Alice has 3 contacts and 2 trusted contacts. Bob has 2 contacts and none are trusted. Alex has 1 trusted contact. Joon has no contacts.

Example 2

Input

customers:
| customer_id | customer_name | email            |
|-------------|---------------|------------------|
| 10          | Nora          | nora@corp.io     |
| 11          | Luis          | luis@corp.io     |

contacts:
| user_id | contact_name | contact_email      |
|---------|--------------|--------------------|
| 10      | Vendor A     | billing@vendor.io  |

invoices:
| invoice_id | price | user_id |
|------------|-------|---------|
| 1          | 80    | 10      |
| 2          | 120   | 11      |

Output

[
  {"invoice_id":1,"customer_name":"Nora","price":80,"contacts_cnt":1,"trusted_contacts_cnt":0},
  {"invoice_id":2,"customer_name":"Luis","price":120,"contacts_cnt":0,"trusted_contacts_cnt":0}
]

Explanation

Customer 10 has one untrusted contact. Customer 11 has no contacts.

Example 3

Input

customers:
| customer_id | customer_name | email            |
|-------------|---------------|------------------|
| 1           | Priya         | priya@data.ai    |
| 2           | Raj           | raj@data.ai      |
| 3           | Mina          | mina@data.ai     |

contacts:
| user_id | contact_name | contact_email   |
|---------|--------------|-----------------|
| 1       | Raj          | raj@data.ai     |
| 1       | Raj Caps     | RAJ@data.ai     |
| 1       | Mina         | mina@data.ai    |

invoices:
| invoice_id | price | user_id |
|------------|-------|---------|
| 7          | 900   | 1       |
| 8          | 300   | 1       |

Output

[
  {"invoice_id":7,"customer_name":"Priya","price":900,"contacts_cnt":3,"trusted_contacts_cnt":2},
  {"invoice_id":8,"customer_name":"Priya","price":300,"contacts_cnt":3,"trusted_contacts_cnt":2}
]

Explanation

Email matching is exact in this setup, so RAJ@data.ai does not match raj@data.ai.

Constraints

0 <= number of rows in customers <= 200000
0 <= number of rows in contacts <= 200000
0 <= number of rows in invoices <= 200000
customer_id is unique in customers
email is unique in customers
(user_id, contact_email) is unique in contacts
invoice_id is unique in invoices
invoices.user_id references an existing customers.customer_id
contacts.user_id references an existing customers.customer_id
1 <= customer_id, user_id, invoice_id, price <= 1000000000
customer_name, email, contact_name, and contact_email are non-empty strings
Return exactly: invoice_id, customer_name, price, contacts_cnt, trusted_contacts_cnt
Order by invoice_id ascending
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with the required output columns