challenge_commision.sql

WITH weights(currency, weight) AS
(
  SELECT 
    DISTINCT ON(currencies)*, (random()*9+1)::decimal
  FROM            
    (
      SELECT unnest(array ['USD', 'PLN', 'PLN', 'PLN', 'PLN', 'EUR', 'CHR'] )
    ) currencies
),
exchange(currency, rates) AS
(
  SELECT
    w1.currency,
    array_agg(
      json_build_object(
        'currency', w2.currency,
        'rate', round(w1.weight/w2.weight, 2)
      )
    )
  FROM
    weights w1
    CROSS JOIN weights w2
  GROUP BY
    w1.currency
)
SELECT
  json_agg(row_to_json(exchange,true))
FROM
  exchange