00:00:00

Description

Editorial

Invoice Contact Trust Coverage Report

MEDIUM20 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

contacts =

[{"user_id":1,"contact_name":"Bob","contact_email":"bob@shop.com"},{"user_id":1,"contact_name":"Joon","contact_email":"joon@shop.com"},{"user_id":1,"contact_name":"Maya","contact_email":"maya@extmail.com"},{"user_id":2,"contact_name":"Omar","contact_email":"omar@extmail.com"},{"user_id":2,"contact_name":"Meir","contact_email":"meir@extmail.com"},{"user_id":6,"contact_name":"Alice","contact_email":"alice@shop.com"}]

invoices =

[{"invoice_id":77,"price":100,"user_id":1},{"invoice_id":88,"price":200,"user_id":1},{"invoice_id":99,"price":300,"user_id":2},{"invoice_id":66,"price":400,"user_id":2},{"invoice_id":55,"price":500,"user_id":13},{"invoice_id":44,"price":60,"user_id":6}]

customers =

[{"customer_id":1,"customer_name":"Alice","email":"alice@shop.com"},{"customer_id":2,"customer_name":"Bob","email":"bob@shop.com"},{"customer_id":13,"customer_name":"Joon","email":"joon@shop.com"},{"customer_id":6,"customer_name":"Alex","email":"alex@shop.com"}]

Loading problem...

101

00:00:00

Description

Editorial

Invoice Contact Trust Coverage Report

MEDIUM20 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

contacts =

invoices =

customers =

Invoice Contact Trust Coverage Report

Hints

Invoice Contact Trust Coverage Report

Hints