· 6 years ago · Jan 05, 2020, 06:32 PM
1-- TASK DESCRIPTION
2-- Given the transactions table and table containing exchange rates:
3
4-- 1. Write down a query that gives us a breakdown of spend in GBP by each user.
5-- Use the exchange rate with largest timestamp less or equal then transaction timestamp.
6
7-- explain analyze
8
9drop table if exists exchange_rates;
10create table exchange_rates(
11ts timestamp without time zone,
12from_currency varchar(3),
13to_currency varchar(3),
14rate numeric
15);
16
17truncate table exchange_rates;
18insert into exchange_rates
19values
20('2018-04-01 00:00:00', 'USD', 'GBP', '0.71'),
21('2018-04-01 00:00:05', 'USD', 'GBP', '0.82'),
22('2018-04-01 00:01:00', 'USD', 'GBP', '0.92'),
23('2018-04-01 01:02:00', 'USD', 'GBP', '0.62'),
24
25('2018-04-01 02:00:00', 'USD', 'GBP', '0.71'),
26('2018-04-01 03:00:05', 'USD', 'GBP', '0.82'),
27('2018-04-01 04:01:00', 'USD', 'GBP', '0.92'),
28('2018-04-01 04:22:00', 'USD', 'GBP', '0.62'),
29
30('2018-04-01 00:00:00', 'EUR', 'GBP', '1.71'),
31('2018-04-01 01:00:05', 'EUR', 'GBP', '1.82'),
32('2018-04-01 01:01:00', 'EUR', 'GBP', '1.92'),
33('2018-04-01 01:02:00', 'EUR', 'GBP', '1.62'),
34
35('2018-04-01 02:00:00', 'EUR', 'GBP', '1.71'),
36('2018-04-01 03:00:05', 'EUR', 'GBP', '1.82'),
37('2018-04-01 04:01:00', 'EUR', 'GBP', '1.92'),
38('2018-04-01 05:22:00', 'EUR', 'GBP', '1.62'),
39
40('2018-04-01 05:22:00', 'EUR', 'HUF', '0.062')
41;
42
43-- For volumes of data close to real, run this:
44insert into exchange_rates (
45select ts, from_currency, to_currency, rate from (
46select date_trunc('second', dd + (random() * 60) * '1 second':: interval) as ts, case when random()*2 < 1 then 'EUR' else 'USD' end as from_currency,
47'GBP' as to_currency, (200 * random():: int )/100 as rate
48FROM generate_series
49 ( '2018-04-01'::timestamp
50 , '2018-04-02'::timestamp
51 , '1 minute'::interval) dd
52 ) a
53where ts not in (select ts from exchange_rates)
54order by ts
55)
56;
57
58-- Transactions
59
60drop table if exists transactions;
61create table transactions (
62ts timestamp without time zone,
63user_id int,
64currency varchar(3),
65amount numeric
66);
67
68truncate table transactions;
69insert into transactions
70values
71('2018-04-01 00:00:00', 1, 'EUR', 2.45),
72('2018-04-01 01:00:00', 1, 'EUR', 8.45),
73('2018-04-01 01:30:00', 1, 'USD', 3.5),
74('2018-04-01 20:00:00', 1, 'EUR', 2.45),
75
76('2018-04-01 00:30:00', 2, 'USD', 2.45),
77('2018-04-01 01:20:00', 2, 'USD', 0.45),
78('2018-04-01 01:40:00', 2, 'USD', 33.5),
79('2018-04-01 18:00:00', 2, 'EUR', 12.45),
80
81('2018-04-01 18:01:00', 3, 'GBP', 2),
82
83('2018-04-01 00:01:00', 4, 'USD', 2),
84('2018-04-01 00:01:00', 4, 'GBP', 2)
85;
86
87-- For volumes of data close to real, run this:
88insert into transactions (
89SELECT dd + (random()*5) * '1 second'::interval as ts, (random() * 1000)::int as user_id,
90case when random()*2 < 1 then 'EUR' else 'USD' end as currency,
91(random() * 10000) :: int / 100 as amount
92FROM generate_series
93 ( '2018-04-01'::timestamp
94 , '2018-04-02'::timestamp
95 , '1 second'::interval) dd
96) ;
97
98
99
100---------------------------
101/*
102
103
104Select x.user_id, sum(spent_gbp) total_spent_gbp
105From
106(
107Select ROW_NUMBER() OVER(
108 PARTITION BY t.user_id, c.from_currency, c.ts
109 ORDER BY c.ts DESC) rn,
110 t.user_id,
111 t.amount*c.rate spent_gbp
112From transactions t
113Join
114(
115Select ts, from_currency, rate
116From exchange_rates
117Where to_currency='GBP'
118Union All
119(Select to_timestamp(0), 'GBP', 1)
120) c
121On t.currency=c.from_currency and c.ts<=t.ts
122) x
123Where x.rn=1
124Group By x.user_id
125
126*/