dbdbexercises
Practice Free
← Syllabus|
Medium

13. Unverified FinTech Accounts Missing Government ID

MediumJoinsPostgreSQL 16PRO
Table: app_users
+---------------------+--------------+
| Column Name         | Type         |
+---------------------+--------------+
| user_id             | int          |
| user_email          | varchar(100) |
| account_tier        | varchar(20)  |
| registered_days_ago | int          |
+---------------------+--------------+

user_id is the unique key for this table. Each row contains record details for this enterprise dataset.

Table: kyc_documents
+-------------+-------------+
| Column Name | Type        |
+-------------+-------------+
| doc_id      | varchar(20) |
| user_id     | int         |
| doc_type    | varchar(30) |
| doc_status  | varchar(20) |
+-------------+-------------+

doc_id is the unique key for this table. Each row contains record details for this enterprise dataset.

Risk and fraud compliance teams need to isolate customer accounts registered over 14 days ago that still have zero approved government identification documents uploaded.

Join `app_users` to `kyc_documents` using a `LEFT JOIN` on `user_id` where `doc_status = 'approved'`. Find users who lack approved KYC verification where `u.registered_days_ago >= 14`. Return: 1. `u.user_id` 2. `u.user_email` 3. `u.account_tier` 4. `u.registered_days_ago` Order results by `u.registered_days_ago` descending, then by `u.user_id` ascending.

Return the result table ordered as specified. The result format is in the following example.

Example 1:
Input:
Preparing sample rows...
Output:
Calculating benchmark target...
Explanation:

Unverified user accounts registered >= 14 days without approved KYC documents.

Constraints:
  • Query must utilize clause(s): LEFT JOINIS NULLORDER BY
  • Result order matters (tie-breakers and sort order must be strictly preserved).
  • Engine: PostgreSQL 16 standard syntax (in-browser WASM).
query.sql|PostgreSQL 16 WASM
Loading SQL editor...
You must run your code first

Write your SQL query in the editor and click Run or Submit to test against in-browser PostgreSQL 16 WASM.

About the “Unverified FinTech Accounts Missing Government ID” SQL Problem

Business Scenario

Risk and fraud compliance teams need to isolate customer accounts registered over 14 days ago that still have zero approved government identification documents uploaded.

Expected Output & Criteria

Unverified user accounts registered >= 14 days without approved KYC documents.

Database Table Schema (PostgreSQL DDL)

PostgreSQL 16 WASM
CREATE TABLE app_users (
        user_id INT PRIMARY KEY,
        user_email VARCHAR(100) NOT NULL,
        account_tier VARCHAR(20) NOT NULL,
        registered_days_ago INT NOT NULL
      );

      CREATE TABLE kyc_documents (
        doc_id VARCHAR(20) PRIMARY KEY,
        user_id INT REFERENCES app_users(user_id),
        doc_type VARCHAR(30) NOT NULL,
        doc_status VARCHAR(20) NOT NULL
      );

Target SQL Clauses & Operators

LEFT JOINIS NULLORDER BY

Want more Joins practice?

Explore the full syllabus of 16 exercises and syntax guides.

View Joins Track