Untitled

    WITH prices_day AS (
      SELECT id, trade_volume_24h_btc, timestamp
      FROM exchange_stats
      WHERE timestamp=(
        SELECT timestamp
        FROM exchange_stats
        WHERE date(timestamp)= date(
        (
        SELECT max(timestamp)
        FROM exchange_stats) - INTERVAL '1' DAY)
        ORDER BY timestamp DESC LIMIT 1
        )
         AND trade_volume_24h_btc != 0
    ), prices_week AS (
      SELECT id, trade_volume_24h_btc, timestamp
      FROM exchange_stats
      WHERE timestamp=(
        SELECT timestamp
        FROM exchange_stats
        WHERE date(timestamp)= date(
        (
        SELECT max(timestamp)
        FROM exchange_stats) - INTERVAL '3' DAY)
        ORDER BY timestamp DESC LIMIT 1
        )
       AND trade_volume_24h_btc != 0
    )
    SELECT
        ex.name,
        es.trade_volume_24h_btc,
        es.trade_volume_24h_btc * (SELECT price FROM latest_coin_stats WHERE cc_id=1)
            AS trade_volume_24h_usd,
        es.trade_volume_24h_btc * 100 / pd.trade_volume_24h_btc - 100 AS trade_volume_day_change,
        es.trade_volume_24h_btc * 100 / pw.trade_volume_24h_btc - 100 AS trade_volume_week_change,
        es.timestamp
    FROM exchanges ex
        LEFT JOIN exchange_stats es on ex.id = es.id
        LEFT JOIN prices_day pd on ex.id = pd.id
        LEFT JOIN prices_week pw on ex.id = pw.id
    WHERE es.trade_volume_24h_btc != 0
      AND es.timestamp=(SELECT max(timestamp) FROM exchange_stats)
      AND es.trade_volume_24h_btc * 100 / pd.trade_volume_24h_btc - 100 != 0
      AND es.trade_volume_24h_btc * 100 / pw.trade_volume_24h_btc - 100 != 0
    ORDER BY trade_volume_day_change DESC , trade_volume_week_change LIMIT {limit};