Home > Mobile >  Select country has the highest account balance
Select country has the highest account balance

Time:06-11

I am new to SQL.I have two tables Customers and deposit_transactions. I am trying to select the country which has the highest account balance for each date. I am joining deposit_transactions table(to get the sum of amount based on customer_id) and joining with customers table to get the Country

Schema (MySQL v8.0)

CREATE TABLE deposit_transactions (
  `deposit_id` VARCHAR(7),
  `customer_id` VARCHAR(5),
  `date` TEXT,
  `transaction_type` VARCHAR(7),
  `amount` INTEGER,
  `currency` VARCHAR(3)
);

INSERT INTO deposit_transactions
  (`deposit_id`, `customer_id`, `date`, `transaction_type`, `amount`, `currency`)
VALUES
  ('DE47653', 'DO900', '1/1/2019', 'pay_in', '10000', 'EUR'),
  ('DE47654', 'DO901', '2/1/2019', 'pay_in', '10000', 'EUR'),
  ('DE47655', 'DO902', '2/1/2019', 'pay_in', '10000', 'EUR'),
  ('UK47656', 'DO903', '3/1/2019', 'pay_in', '10000', 'GBP'),
  ('UK47657', 'DO904', '3/1/2019', 'pay_in', '130000', 'GBP'),
  ('DE47658', 'DO905', '3/1/2019', 'pay_in', '140000', 'EUR'),
  ('DE47659', 'DO906', '3/1/2019', 'pay_in', '10000', 'EUR'),
  ('DE47660', 'DO907', '3/1/2019', 'pay_in', '10000', 'EUR'),
  ('DE47661', 'DO908', '3/1/2019', 'pay_in', '100000', 'EUR'),
  ('DE47662', 'DO909', '4/1/2019', 'pay_in', '10000', 'EUR'),
  ('UK47663', 'DO910', '4/1/2019', 'pay_in', '10000', 'GBP'),
  ('UK47664', 'DO911', '4/1/2019', 'pay_in', '5000', 'GBP'),
  ('UK47665', 'DO912', '4/1/2019', 'pay_in', '4000', 'GBP'),
  ('DE47666', 'DO913', '5/1/2019', 'pay_in', '10000', 'EUR'),
  ('DE47661', 'DO908', '5/1/2019', 'pay_out', '50000', 'EUR'),
  ('DE47667', 'DO914', '11/1/2019', 'pay_in', '30000', 'EUR'),
  ('DE47668', 'DO915', '11/1/2019', 'pay_in', '10000', 'EUR'),
  ('DE47669', 'DO916', '11/1/2019', 'pay_in', '25000', 'EUR'),
  ('DE47670', 'DO917', '11/1/2019', 'pay_in', '50000', 'EUR'),
  ('DE47667', 'DO914', '12/1/2019', 'pay_out', '7000', 'EUR'),
  ('DE47667', 'DO914', '12/15/2019', 'pay_in', '12000', 'EUR'),
  ('DE47671', 'DO918', '1/1/2021', 'pay_in', '9000', 'EUR'),
  ('DE47672', 'DO919', '1/1/2021', 'pay_in', '10000', 'EUR'),
  ('DE47673', 'DO920', '1/1/2021', 'pay_in', '11000', 'EUR'),
  ('DE47674', 'DO921', '1/1/2021', 'pay_in', '12000', 'EUR'),
  ('DE47675', 'DO922', '1/1/2021', 'pay_in', '13000', 'EUR'),
  ('DE47676', 'DO923', '1/1/2021', 'pay_in', '14000', 'EUR'),
  ('DE47677', 'DO924', '1/1/2021', 'pay_in', '30000', 'EUR'),
  ('DE47678', 'DO925', '1/1/2021', 'pay_in', '16000', 'EUR');

CREATE TABLE customers (
  `customer_id` VARCHAR(5),
  `country` VARCHAR(7)
);

INSERT INTO customers
  (`customer_id`, `country`)
VALUES
  ('DO900', 'Germany'),
  ('DO901', 'Germany'),
  ('DO902', 'Spain'),
  ('DO903', 'UK'),
  ('DO904', 'UK'),
  ('DO905', 'Austria'),
  ('DO906', 'Germany'),
  ('DO907', 'Germany'),
  ('DO908', 'Germany'),
  ('DO909', 'Germany'),
  ('DO910', 'UK'),
  ('DO911', 'UK'),
  ('DO912', 'UK'),
  ('DO913', 'Germany'),
  ('DO914', 'Austria'),
  ('DO915', 'Germany'),
  ('DO916', 'Austria'),
  ('DO917', 'Germany'),
  ('DO918', 'Germany'),
  ('DO919', 'Spain'),
  ('DO920', 'Germany'),
  ('DO921', 'Spain'),
  ('DO922', 'Germany'),
  ('DO923', 'Germany'),
  ('DO924', 'Germany'),
  ('DO925', 'Spain'),
  ('DO926', 'Germany'),
  ('DO927', 'Germany'),
  ('DO928', 'Germany'),
  ('DO929', 'Germany'),
  ('DO711', 'UK'),
  ('DO712', 'UK'),
  ('DO713', 'Germany'),
  ('DO714', 'Austria'),
  ('DO715', 'Germany'),
  ('DO716', 'Austria'),
  ('DO717', 'Germany'),
  ('DO718', 'Germany'),
  ('DO719', 'Spain'),
  ('DO720', 'Germany'),
  ('DO721', 'Spain'),
  ('DO722', 'Germany'),
  ('DO723', 'Germany'),
  ('DO724', 'Germany'),
  ('DO725', 'Spain'),
  ('DO726', 'Germany'),
  ('DO727', 'Germany'),
  ('DO728', 'Germany'),
  ('DO729', 'Germany');

Below is the sample query I created. But I am not sure if its correct

Query

SELECT t.customer_id,
       c.country,t.total_amount
FROM  (SELECT customer_id,
              date,
              transaction_type,
              Sum(amount)
                OVER (
                  partition BY customer_id ) AS total_amount_transfered,
              Row_number()
                OVER (
                  partition BY customer_id
                  ORDER BY date DESC)        AS n
       FROM   deposit_transactions)t
      INNER JOIN customers AS c
              ON c.customer_id = t.customer_id
WHERE  t.n = 1
       AND t.transaction_type = 'pay_in' order by total_amount desc limit 1;

CodePudding user response:

Step 1. Finding the total balance amount for each country.

Since you need to get the balance and you have two kinds of transactions, I'd rather transform the "pay-out" transactions into negative balance and leave "pay-in" transactions as are. Also you should take into account the different currencies, because "GBP" currency has a higher value than "EUR" currency (GBP ~ EUR*0.85). If you don't want to consider "pay-out" negative impact, you can transform them to 0. This balance wrangling is done here by a CASE statement.

Then the balances are aggregated with a SUM function over the corresponding countries (accessible because of the JOIN operation) and grouped over the "country" and "date" field.

SELECT country,
       date AS transaction_date,
--     STR_TO_DATE(date, '%m/%d/%Y') AS transaction_date,
       SUM(CASE WHEN transaction_type = 'pay_in' AND currency = 'EUR' THEN amount 
                WHEN transaction_type = 'pay_in' AND currency = 'GBP' THEN amount/0.85
--              WHEN transaction_type = 'pay_out'                      THEN 0
                WHEN transaction_type = 'pay_out' AND currency = 'EUR' THEN -amount
                WHEN transaction_type = 'pay_out' AND currency = 'GBP' THEN -amount/0.85 
           END) AS amount_eur
FROM       deposit_transactions dt
INNER JOIN customers c
        ON dt.customer_id = c.customer_id
GROUP BY country,
         transaction_date

Optionally in this step, you could also transform the "date" field from the TEXT type to the DATE type using the STR_TO_DATE function.


Step 2. Ranking the countries over the summed amounts.

In order to apply a rank, you use the ROW_NUMBER window function, to partition on the transaction date and order by the balance amount descendently.

SELECT *, ROW_NUMBER() OVER(
              PARTITION BY transaction_date
              ORDER     BY amount_eur DESC ) AS rn
FROM   total_amounts_per_country

Step 3. Selecting the country having the highest balance.

You can just all rows which have the rank equal to 1, given that there will be a 1 for each date. Then select the interesting fields, namely "transaction_date" and "country".

SELECT transaction_date, 
       country
FROM ranked_countries
WHERE rn = 1

Full Query. It uses MySQL common table expressions to combine the three steps.

WITH total_amounts_per_country AS(
    SELECT country,
           date AS transaction_date,
--         STR_TO_DATE(date, '%m/%d/%Y') AS transaction_date,
           SUM(CASE WHEN transaction_type = 'pay_in' AND currency = 'EUR' THEN amount 
                    WHEN transaction_type = 'pay_in' AND currency = 'GBP' THEN amount/0.85
--                  WHEN transaction_type = 'pay_out'                      THEN 0
                    WHEN transaction_type = 'pay_out' AND currency = 'EUR' THEN -amount
                    WHEN transaction_type = 'pay_out' AND currency = 'GBP' THEN -amount/0.85               END) AS amount_eur
    FROM       deposit_transactions dt
    INNER JOIN customers c
            ON dt.customer_id = c.customer_id
    GROUP BY country,
             transaction_date
), ranked_countries AS (
    SELECT *, ROW_NUMBER() OVER(
                  PARTITION BY transaction_date
                  ORDER     BY amount_eur DESC ) AS rn
    FROM   total_amounts_per_country
)
SELECT transaction_date, 
       country
FROM ranked_countries
WHERE rn = 1

Check the demo here.

  • Related