How do I partition my database by rowid and query the partitions properly?

Obligatory I'm a beginner at database management. To be specific about my situation, I'm working in SQLite3 in Python as a means to an end for now for a finance project. The table is called transactions and it stores a market_id, price, and quantity, where each price-quantity pair is a tick. Inserting ticks into the table works perfectly, querying specific data feels nearly impossible for what I'm trying to do. What I'm trying to do: - Find all prices and quantities in a market by market_id - Partition prices and quantities by the floor division of their respective row ID by a variable amount - Find the first, highest, lowest, and last price and the sum of all quantities in the partitions - Return the last n queried partitions To quickly summarize, get the latest OHLCV information in tick bars. What I've tried so far: Attempt 1:

SELECT
  (SELECT price WHERE group_id = 1),
   MAX(price) AS high,
   MIN(price) AS low,
  (SELECT price WHERE group_id = 0),
   SUM(quantity) AS volume
FROM (
   SELECT
     price,
     quantity,
     ROWID as id,
     (ROWID % ?) as group_id,
     CAST((ROWID - 1) / ? AS INTEGER) AS group_partition
     FROM transactions
     WHERE market_id = ?
      ORDER BY id DESC
   )
GROUP BY
  group_partition
ORDER BY
  id DESC
LIMIT ?

Seemed promising but gave null values so likely isn't good code. Attempt 2:

SELECT
  FIRST_VALUE(price) OVER (PARTITION BY group_id) AS open,
  MAX(price) AS high,
  MIN(price) AS low,
  LAST_VALUE(price) OVER (PARTITION BY group_id) AS close,
  SUM(quantity) AS volume,
  MIN(id) AS min_id
FROM (
  SELECT
    price,
    quantity,
    ROWID as id,
    CAST((ROWID - 1) / ? AS INTEGER) AS group_id
    FROM transactions
    WHERE market_id = ?
    ORDER BY id ASC
)
GROUP BY group_id
ORDER BY min_id DESC
LIMIT ?

Gave incorrect open and close prices when I investigated further. For instance, if I had a partition of prices as an array [100.12, 99.96, 99.98, 100.05, 99.9] in that order I would get [100.12, 100.12, 99.9, 100.12] as the open, high, low, and close, where the close was just the same as the open. And many other attempts. To put it simply, I've been working on this for over 3 hours and can't figure it out, so any help or suggestions is greatly appreciated. Edit for correct query and output as explained by Andrea B.:

SELECT
    open,
    MAX(price) AS high,
    MIN(price) AS low,
    close,
    SUM(quantity) AS volume,
    MIN(id) AS min_id
FROM (
    SELECT
        price,
        quantity,
        ROWID as id,
        (ROWID - 1) / ? AS group_id,
        FIRST_VALUE(price) OVER (PARTITION BY (ROWID - 1) / ? ORDER BY ROWID) AS open,
        FIRST_VALUE(price) OVER (PARTITION BY (ROWID - 1) / ? ORDER BY ROWID DESC) AS close
        FROM transactions
        WHERE market_id = ?
        ORDER BY id DESC
)
GROUP BY group_id
ORDER BY min_id DESC
LIMIT ?

And it's output Data Array (1 partition): [99.76, 99.76, 99.71, 99.79, 99.87] Output: [99.76, 99.87, 99.71, 99.87]