Skip to content

Instantly share code, notes, and snippets.

@spacecowb0y
Last active April 9, 2024 14:30
Show Gist options
  • Save spacecowb0y/b2da139cdfb4d7154fec26030ee95e1f to your computer and use it in GitHub Desktop.
Save spacecowb0y/b2da139cdfb4d7154fec26030ee95e1f to your computer and use it in GitHub Desktop.
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment