Revere Visual Builder Article

final-sql.sql

SELECT 
  cast(MAIN.metro_region as string) as __metro_region__,
  cast(FORMAT("%'d",SUM(MAIN.sessions)) as string) as __sessions__,
  cast(FORMAT("%'d",SUM(MAIN.transactions)) as string) as __transactions__,
  cast(CONCAT("$", format("%'.2f",SUM(MAIN.transaction_revenue))) as string) as __transaction_revenue__,FORMAT_DATE("%m/%d/%Y", date(MAIN.datehour) ) as __datehour__ 
FROM ecommerce.ecommerce as MAIN, 
  (SELECT SUM(transactions) as sum1 , metro_region FROM ecommerce.ecommerce  WHERE date(datehour) >= DATE_ADD(CURRENT_DATE, INTERVAL -7 DAY)  AND date(datehour) <> CURRENT_DATE GROUP BY metro_region) as SUB1 
WHERE ( (MAIN.sessions >= 10)  or (MAIN.transactions > 5) ) 
AND date(MAIN.datehour) = DATE_ADD(CURRENT_DATE, INTERVAL -1 DAY) 
AND  (MAIN.metro_region = SUB1.metro_region) 
GROUP BY MAIN.metro_region,__datehour__ 
HAVING (SUM(MAIN.transactions) < (MAX(SUB1.sum1)))  
ORDER BY SUM(MAIN.sessions) DESC