Interview

Question: Building a Customer Analytics System

You are building a customer analytics system for an e-commerce platform. The system needs to evolve from a simple reporting tool to a flexible, reusable analytics engine. Complete the following parts in sequence — each part builds upon the previous one.

Part A: Basic Data Retrieval Logic

The marketing team needs a list of all customers who placed an order in the last 30 days, showing their Name, Email, and the Total Amount spent.

SQL Task:

Write a PostgreSQL query that:

  • Joins customers and orders tables
  • Filters by order_date within the last 30 days using CURRENT_DATE - INTERVAL '30 days'
  • Uses SUM() and GROUP BY to calculate total spend

API Task

Create a REST API endpoint that:

  • Exposes GET /api/customers/recent-orders
  • Executes the PostgreSQL query from above
  • Returns a JSON response
  • Handles DB connection and error cases gracefully

Part B: Making it Reusable & Dynamic

The admin dashboard needs a flexible search tool. Instead of writing new queries for every combination, you must make the Part A query dynamic.

SQL Task:

Create a PostgreSQL function search_orders() that:

  • Accepts customer_id INTEGER DEFAULT NULL
  • Accepts min_amount DECIMAL DEFAULT NULL
  • Accepts status VARCHAR DEFAULT NULL
  • Ignores filters where the value is NULL
  • Filters by exact match for status
  • Filters by amount > min_amount
  • Extends Part A's query dynamically
CREATE OR REPLACE FUNCTION search_orders(
    customer_id INTEGER DEFAULT NULL,
    min_amount DECIMAL DEFAULT NULL,
    status VARCHAR DEFAULT NULL
) RETURNS TABLE(...) AS $$

API Task

Create an endpoint that:

  • Exposes /api/orders/search (GET or POST)
  • Accepts optional parameters
  • Passes them to search_orders()
  • Handles null/optional values
  • Includes validation & error handling

Part C: Abstracted Metadata Architecture

The company wants a No-Code Report Builder. The frontend will send JSON describing filters for ANY table, and the backend must generate SQL dynamically.

SQL Task:

You must support this input:

{
  "view_name": "SalesView",
  "filters": [
    { "field": "Region", "op": "EQ", "val": "US-East" },
    { "field": "Revenue", "op": "GT", "val": 1000 }
  ]
}

Your solution must include:

  • Metadata tables for friendly field ? actual column
  • Dynamic SQL using EXECUTE & parameterization (USING)
  • Validation for supported operations
  • SQL Injection protection
  • Indexing strategy (B-Tree, GIN for JSONB if needed)

API Task

Create a dynamic endpoint that:

  • Exposes POST /api/reports/generate
  • Validates JSON and metadata
  • Uses the dynamic SQL engine
  • Returns paginated results
  • Handles all error conditions gracefully
  • Prevents SQL injection
  • Optional: Add caching for recurring reports