📊 Statistik

Custom SQL query returning 6 rows (hide)

SELECT
  'Gesamt-Statistik' AS "Kategorie",
  '' AS "Detail",
  '' AS "Wert"
UNION ALL
SELECT
  'Produkte',
  'Anzahl unterschiedlicher Produkte',
  CAST(COUNT(DISTINCT id) AS TEXT)
FROM product
UNION ALL
SELECT
  'Händler',
  'Anzahl Händler',
  CAST(COUNT(*) AS TEXT)
FROM retailer
UNION ALL
SELECT
  'Preisbeobachtungen',
  'Gesamt-Anzahl',
  CAST(COUNT(*) AS TEXT)
FROM price_observation
UNION ALL
SELECT
  'Aktionen',
  'Aktuelle Angebote',
  CAST(SUM(CASE WHEN is_promo = 1 THEN 1 ELSE 0 END) AS TEXT)
FROM price_observation po
JOIN (
  SELECT product_market_id, MAX(observed_date) AS latest_date
  FROM price_observation
  GROUP BY product_market_id
) latest ON po.product_market_id = latest.product_market_id
  AND po.observed_date = latest.latest_date
UNION ALL
SELECT
  'Letztes Update',
  '',
  MAX(observed_date)
FROM price_observation;

Edit SQL