Skip to content

Outstanding invoices 💱

Scenario

A consulting firm, based in the US, works with clients all over the world.

They invoice their clients in USD and are interested in the total amount outstanding in each local currency.

Question

Calculate the total amount outstanding (is_paid = false) in each local currency for the invoices in the invoices table.

Use the exchange_rates table to convert the invoice amount (USD) to the local currency, using the latest exchange rate at the time of the invoice.

The output should have a row per invoice_currency with the columns:

  • invoice_currency
  • amount_outstanding as the total amount outstanding in the local currency, rounded up to two decimal places

Order the output by invoice_currency.

Expand for the DDL 
create table invoices (
    invoice_id         integer primary key,
    invoice_datetime   timestamp not null,
    invoice_amount_usd numeric(10, 2) not null,
    is_paid            boolean not null,
    invoice_currency   varchar not null
);
insert into invoices
values
    (87, '2024-05-02 14:59:21', 38599.00, true,  'INR'),
    (88, '2024-05-05 15:01:22',   199.99, false, 'GBP'),
    (89, '2024-05-06 02:24:34',  1200.50, false, 'INR'),
    (90, '2024-05-07 11:59:59',   299.99, true,  'GBP'),
    (91, '2024-05-18 15:48:17', 24000.00, true,  'INR'),
    (92, '2024-05-23 05:12:22',  2499.50, false, 'USD'),
    (93, '2024-05-24 23:35:34',    50.00, false, 'INR'),
    (94, '2024-06-03 11:52:01',   150.00, true,  'GBP'),
    (95, '2024-06-04 01:11:52',  8999.99, false, 'INR'),
    (96, '2024-06-17 21:01:29',   720.00, false, 'USD'),
    (97, '2024-06-18 00:12:50', 75000.00, false, 'GBP'),
    (98, '2024-06-28 07:19:23',  8000.00, false, 'USD')
;


create table exchange_rates (
    from_datetime timestamp,
    from_currency varchar,
    to_currency   varchar,
    rate          numeric(10, 4) not null,
    primary key (from_datetime, from_currency, to_currency)
);
insert into exchange_rates
values
    ('2024-05-01 22:45:17', 'USD', 'INR', 83.4550),
    ('2024-05-01 19:31:12', 'USD', 'GBP',  0.7983),
    ('2024-05-06 15:17:07', 'USD', 'INR', 83.4750),
    ('2024-05-10 06:48:57', 'USD', 'GBP',  0.7984),
    ('2024-05-10 11:03:02', 'USD', 'INR', 83.5410),
    ('2024-05-13 02:34:52', 'USD', 'INR', 83.4600),
    ('2024-05-17 18:06:42', 'USD', 'GBP',  0.7872),
    ('2024-05-17 22:20:47', 'USD', 'INR', 83.2800),
    ('2024-05-20 09:38:32', 'USD', 'GBP',  0.7869),
    ('2024-05-20 13:52:37', 'USD', 'INR', 83.2800),
    ('2024-05-24 01:10:22', 'USD', 'GBP',  0.7849),
    ('2024-05-24 05:24:27', 'USD', 'INR', 83.0600),
    ('2024-05-27 16:42:12', 'USD', 'GBP',  0.7831),
    ('2024-05-27 20:56:17', 'USD', 'INR', 83.1020),
    ('2024-05-31 08:14:02', 'USD', 'GBP',  0.7846),
    ('2024-05-31 12:28:07', 'USD', 'INR', 83.4240),
    ('2024-06-03 00:45:52', 'USD', 'GBP',  0.7808),
    ('2024-06-03 04:59:57', 'USD', 'INR', 83.0830),
    ('2024-06-06 20:31:47', 'USD', 'INR', 83.4660),
    ('2024-06-07 16:17:42', 'USD', 'INR', 83.5210),
    ('2024-06-10 12:03:37', 'USD', 'GBP',  0.7854),
    ('2024-06-14 03:35:27', 'USD', 'GBP',  0.7880),
    ('2024-06-14 07:49:32', 'USD', 'INR', 83.5470),
    ('2024-06-17 23:21:22', 'USD', 'GBP',  0.7870),
    ('2024-06-18 19:07:17', 'USD', 'GBP',  0.7867),
    ('2024-06-19 14:53:12', 'USD', 'INR', 83.3300),
    ('2024-06-22 10:39:07', 'USD', 'GBP',  0.7860),
    ('2024-06-23 06:25:02', 'USD', 'INR', 83.4600),
    ('2024-06-24 02:10:57', 'USD', 'GBP',  0.7900),
    ('2024-06-28 21:56:52', 'USD', 'GBP',  0.7909),
    ('2024-06-29 17:42:47', 'USD', 'INR', 83.5680)
;

The solution can be found at:

Sample input

Invoices

invoice_id invoice_datetime invoice_amount_usd is_paid invoice_currency
1 2024-06-03 11:52:01 100.00 true USD
2 2024-06-04 01:11:52 200.00 false INR
3 2024-06-17 21:01:29 300.00 false USD
4 2024-06-18 00:12:50 499.00 false GBP

Exchange Rates

from_datetime from_currency to_currency rate
2024-06-02 00:45:52 USD GBP 0.7808
2024-06-02 04:59:57 USD INR 83.0830
2024-06-14 03:35:27 USD GBP 0.7880
2024-06-14 07:49:32 USD INR 83.5470
2024-06-17 23:21:22 USD GBP 0.7870
2024-06-28 21:56:52 USD GBP 0.7909
2024-06-29 17:42:47 USD INR 83.5680 
with

invoices(invoice_id, invoice_datetime, invoice_amount_usd, is_paid, invoice_currency) as (
    values
        (1, '2024-06-03 11:52:01'::timestamp, 100.00, true,  'USD'),
        (2, '2024-06-04 01:11:52'::timestamp, 200.00, false, 'INR'),
        (3, '2024-06-17 21:01:29'::timestamp, 300.00, false, 'USD'),
        (4, '2024-06-18 00:12:50'::timestamp, 499.00, false, 'GBP')
),

exchange_rates(from_datetime, from_currency, to_currency, rate) as (
    values
        ('2024-06-02 00:45:52'::timestamp, 'USD', 'GBP',  0.7808),
        ('2024-06-02 04:59:57'::timestamp, 'USD', 'INR', 83.0830),
        ('2024-06-14 03:35:27'::timestamp, 'USD', 'GBP',  0.7880),
        ('2024-06-14 07:49:32'::timestamp, 'USD', 'INR', 83.5470),
        ('2024-06-17 23:21:22'::timestamp, 'USD', 'GBP',  0.7870),
        ('2024-06-28 21:56:52'::timestamp, 'USD', 'GBP',  0.7909),
        ('2024-06-29 17:42:47'::timestamp, 'USD', 'INR', 83.5680)
)
Sample output
invoice_currency amount_outstanding
GBP 392.72
INR 16616.60
USD 300.00 
solution(invoice_currency, amount_outstanding) as (
    values
        ('GBP',   392.72),
        ('INR', 16616.60),
        ('USD',   300.00)
)
Hint 1

For databases that support it, use an ASOF join to use the latest exchange rate for each invoice (as of the time of the invoice).

For databases that don't support ASOF joins, use any other method to get the exchange rates for each invoice, such as a correlated subquery.

Hint 2

Use the CEIL function (or equivalent) with the value to round multiplied by 100, then divide by 100 to round up to two decimal places.