## Problem You have these tables: ```sql -- invoices(id, client_id, amount, issued_date, due_date, paid_date) -- paid_date NULL if unpaid -- clients(id, name, country) ``` **Query 1:** List all overdue invoices (due date passed, not paid) with client name, amount, and days overdue. ```sql SELECT c.name AS client, i.amount, DATEDIFF(CURRENT_DATE, i.due_date) AS days_overdue FROM invoices i JOIN clients c ON i.client_id = c.id WHERE i.paid_date IS NULL AND i.due_date < CURRENT_DATE ORDER BY days_overdue DESC; ``` **Query 2:** For each country, return total outstanding balance and number of overdue invoices. ```sql SELECT c.country, COUNT(*) AS overdue_count, SUM(i.amount) AS outstanding_balance FROM invoices i JOIN clients c ON i.client_id = c.id WHERE i.paid_date IS NULL AND i.due_date < CURRENT_DATE GROUP BY c.country ORDER BY outstanding_balance DESC; ``` ## Follow-ups 1. A client partially pays an invoice (payments table). Rewrite Query 1 to show remaining balance. 2. Add a 30/60/90-day aging bucket column to Query 1. 3. How would you set up a scheduled job to email overdue invoice reports? 4. Write the equivalent of Query 1 in Python pandas without SQL.

InterviewDB Experience

Invoice SQL: Query an Invoicing System to Report Overdue Invoices and Outstanding Balances

Interview Experience

Problem You have these tables: Query 1: List all overdue invoices (due date passed, not paid) with client name, amount, and days overdue. Query 2: For each country, return total outstanding balance and number of overdue invoices. Follow-ups A client partially pays an invoice (payments table). Rewrite Query 1 to show remaining balance. Add a 30/60/90-day aging bucket column to Query 1. How would you set up a scheduled job to email overdue invoice reports? Write the equivalent of Query 1 in Python…

Full Details

🔒

Unlock all Square/Block questions

Full insider details, leaked discussions, and candidate experiences.

or every company, $100/year →

About This Question

This is a candidate experience report from a square/block interview.

It covers the following topics: Data Scientist, Sql .

Topics

Data Scientist Sql

More Square/Block Interview Questions

← All Square/Block questions Square/Block Data Scientist Questions → All Sql Questions →