WITH (
  SELECT 
    products.product_name,
    products.product_manufacturer,
    toStartOfDay(events.timestamp) AS day,
    sum(events.sales_price) AS total_revenue
  FROM products
  INNER JOIN events on events.product_id = products.id
) AS product_totals
SELECT
  product_name,
  total_revenue
FROM product_totals
WHERE day >= now() - INTERVAL 7 day
AND product_name like '%shoes%'