Leetcode Problem 1364. Number of Trusted Contacts of a Customer

1364. Number of Trusted Contacts of a Customer

Leetcode Solutions

Joins, Group By, and Conditional Aggregation

Join the Invoices table with the Customers table on user_id to customer_id to get the customer names for each invoice.
Perform a left join with the Contacts table on user_id to get all contacts for each customer.
Use a subquery in the SELECT clause to count the number of trusted contacts by checking if the contact's email exists in the Customers table.
Use the GROUP BY clause to group results by invoice_id.
Use conditional aggregation to count the total number of contacts and the number of trusted contacts.
Order the final result by invoice_id.

erDiagram
    Customers ||--o{ Invoices : has
    Customers ||--o{ Contacts : has
    Invoices {
        int invoice_id PK
        int price
        int user_id FK
    }
    Customers {
        int customer_id PK
        varchar customer_name
        varchar email
    }
    Contacts {
        int user_id PK
        varchar contact_name
        varchar contact_email PK
    }

Using Common Table Expressions (CTEs) for Clarity

Ask Question

Programming Language

Purpose:

General Question

Debug My Code

image/screenshot of info(optional)

Full Screen

Leetcode Problem 1364. Number of Trusted Contacts of a Customer

Leetcode Solutions

Joins, Group By, and Conditional Aggregation

Solution Idea

Query Steps

Code Implementation

Using Common Table Expressions (CTEs) for Clarity

Ask Question

Suggested Answer