prices.sql

WITH PriceExtremes AS (
  SELECT
    productoId,
    MIN(CAST(precio AS REAL)) AS min_price,
    MAX(CAST(precio AS REAL)) AS max_price
  FROM Precios
  GROUP BY productoId
)

SELECT
  p.uuid AS product_uuid,
  p.nombre AS product_name,
  pe.min_price AS min_price,
  pe.max_price AS max_price,
  (
    ROUND(((pe.max_price - pe.min_price) / pe.min_price) * 10000) / 100 || '%' 
  ) AS price_variation_percentage,
  (
    SELECT pr.fecha
    FROM Precios AS pr
    WHERE pr.productoId = p.uuid AND CAST(pr.precio AS REAL) = pe.min_price
    LIMIT 1
  ) AS min_price_date,
  (
    SELECT pr.fecha
    FROM Precios AS pr
    WHERE pr.productoId = p.uuid AND CAST(pr.precio AS REAL) = pe.max_price
    LIMIT 1
  ) AS max_price_date
FROM
  Productos AS p
JOIN
  PriceExtremes AS pe ON p.uuid = pe.productoId
ORDER BY
  price_variation_percentage DESC
LIMIT 100;