How do I nest these queries in one Replace Into query?

Mitchell Spencer 0
Viewed 34

I have three queries and another table called output_table. This code works but needs to be executed in 1. REPLACE INTO query. I know this involves nested and subqueries, but I have no idea if this is possible since my key is the DISTINCT coins datapoints from target_currency.

How to rewrite 2 and 3 so they execute in query 1? That is, the REPLACE INTO query instead of the individual UPDATE ones:

1. conn3.cursor().execute(
    """REPLACE INTO coin_best_returns(coin) SELECT DISTINCT target_currency FROM output_table"""
)

2. conn3.cursor().execute(
    """UPDATE coin_best_returns SET
    highest_price = (SELECT MAX(ask_price_usd) FROM output_table WHERE coin_best_returns.coin = output_table.target_currency),
    lowest_price = (SELECT MIN(bid_price_usd) FROM output_table WHERE coin_best_returns.coin = output_table.target_currency)"""
)

3. conn3.cursor().execute(
    """UPDATE coin_best_returns SET
        highest_market = (SELECT exchange FROM output_table WHERE coin_best_returns.highest_price = output_table.ask_price_usd),
        lowest_market = (SELECT exchange FROM output_table WHERE coin_best_returns.lowest_price = output_table.bid_price_usd)"""
)
nested sqlite sql python
1 Answers

You can do it with the help of some window functions, a subquery, and an inner join. The version below is pretty lengthy, but it is less complicated than it may appear. It uses window functions in a subquery to compute the needed per-currency statistics, and factors this out into a common table expression to facilitate joining it to itself.

Other than the inline comments, the main reason for the complication is original query number 3. Queries (1) and (2) could easily be combined as a single, simple, aggregate query, but the third query is not as easily addressed. To keep the exchange data associated with the corresponding ask and bid prices, this query uses window functions instead of aggregate queries. This also provides a vehicle different from DISTINCT for obtaining one result per currency.

Here's the bare query:

WITH output_stats AS (
    -- The ask and bid information for every row of output_table, every row
    -- augmented by the needed maximum ask and minimum bid statistics
    SELECT
      target_currency as tc,
      ask_price_usd as ask,
      bid_price_usd as bid,
      exchange as market,
      MAX(ask_price_usd) OVER (PARTITION BY target_currency) as high,
      ROW_NUMBER() OVER (
        PARTITION_BY target_currency, ask_price_usd ORDER BY exchange DESC)
        as ask_rank
      MIN(bid_price_usd) OVER (PARTITION BY target_currency) as low,
      ROW_NUMBER() OVER (
        PARTITION_BY target_currency, bid_price_usd ORDER BY exchange ASC)
        as bid_rank
    FROM output_table
  )
REPLACE INTO coin_best_returns(
  -- you must, of course, include all the columns you want to fill in the
  -- upsert column list
  coin,
  highest_price,
  lowest_price,
  highest_market,
  lowest_market)
SELECT
  -- ... and select a value for each column
  asks.tc,
  asks.ask,
  bids.bid,
  asks.market,
  bids.market
FROM output_stats asks
  JOIN output_stats bids
    ON asks.tc = bids.tc
WHERE
  -- These conditions choose exactly one asks row and one bids row
  -- for each currency
  asks.ask = asks.high
  AND asks.ask_rank = 1
  AND bids.bid = bids.low
  AND bids.bid_rank = 1

Note well that unlike the original query 3, this will consider only exchange values associated with the target currency for setting the highest_market and lowest_market columns in the destination table. I'm supposing that that's what you really want, but if not, then a different strategy will be needed.

John Bollinger0
Related