DB Exercises
0
← Exercises/
Window Functions•7 of 14
PrevNext
Window FunctionsAdvancedPRO

Card Transaction Velocity with Window COUNT()

Scenario

Automated credit card fraud bots test stolen card numbers by making rapid micro-transactions in quick succession. Risk engineers use cumulative window counts to detect card usage frequency.

Task
For each transaction in `card_transactions`, compute the cumulative count of transactions executed on that specific card up to and including the current transaction. Return: 1. `txn_id` 2. `card_token` 3. `merchant_name` 4. `amount_usd` 5. `card_txn_sequence`: `COUNT(*) OVER (PARTITION BY card_token ORDER BY txn_time ASC)` Order results by `card_token` ascending, then by `card_txn_sequence` ascending.
Expected Output Format

Transaction log showing the incremental card usage sequence number.

Input Tables & Sample Data0 tables
Loading database schema...
Need a hint?
query.sql|Local Sandbox
Loading SQL editor...
Click "Run" or press ⌘ + Enter to execute your query

About the “Card Transaction Velocity with Window COUNT()” SQL Problem

Business Scenario

Automated credit card fraud bots test stolen card numbers by making rapid micro-transactions in quick succession. Risk engineers use cumulative window counts to detect card usage frequency.

Expected Output & Criteria

Transaction log showing the incremental card usage sequence number.

Database Table Schema (PostgreSQL DDL)

PostgreSQL 16 WASM

This challenge executes directly against the following relational schema in your browser:

CREATE TABLE card_transactions (
        txn_id INT PRIMARY KEY,
        card_token VARCHAR(20) NOT NULL,
        merchant_name VARCHAR(80) NOT NULL,
        amount_usd NUMERIC(10, 2) NOT NULL,
        txn_time TIMESTAMP NOT NULL
      );

Target SQL Clauses & Operators

COUNTOVERPARTITION BY

Want more Window Functions practice?

Explore the full syllabus of 14 exercises, syntax guides, and interview pitfalls.

View Window Functions Track→