-- Create a materialized view to continuously calculate portfolio positions.
CREATE MATERIALIZED VIEW portfolio_monitoring AS
SELECT
  o.user_id,
  o.asset_id,
  SUM(o.quantity) AS net_quantity,
  md.price AS current_price,
  SUM(o.quantity) * md.price AS position_value
FROM
  orders AS o
JOIN
  market_data AS md
ON
  o.asset_id = md.asset_id
GROUP BY
  o.user_id,
  o.asset_id,
  md.price;

# Create a materialized view for average stock prices over 1-minute intervals
mv = rw.mv(
  name="avg_stock_price_mv",
  stmt="""
    SELECT
      window_start,
      window_end,
      symbol,
      ROUND(AVG(price), 2) AS avg_price
    FROM
      TUMBLE(stock_trades, timestamp, INTERVAL '1 minute')
    GROUP BY
      window_start,
      window_end,
      symbol
  """
)

# Fetch the result into a pandas DataFrame
result: pd.DataFrame = rw.fetch("""
  SELECT
    window_start,
    window_end,
    symbol,
    avg_price
  FROM
    avg_stock_price_mv
""", format=OutputFormat.DATAFRAME)

# Display the result
print(result)