SELECT
  product_dim.product_type,
  product_dim.price,
  count(purchase_fact.purchase_fact_id)
FROM
  product_dim INNER JOIN purchase_fact ON (product_dim.product_dim_id=purchase_fact.product_dim_id)
   INNER JOIN (
  select
message_id
,subscriber_id
,product_id
,ticket_id
,name
,data
,message_type
,status
,client_params
,source
,created_at
,updated_at
from messages_fact
  )  d_Messages_Dim ON (d_Messages_Dim.message_id=purchase_fact.message_id)

WHERE
  (
   date(d_Messages_Dim.created_at)  BETWEEN  @prompt('Enter Start Date:','D',,Mono,Free,Persistent,,User:0)  AND  @prompt('Enter End Date:','D',,Mono,Free,Persistent,,User:1)
   AND
   d_Messages_Dim.message_type  In  ( 'SCHEDULED','INITAL OFFERS','OnDemand','VIEW_TRIGGER'  )
   AND
   (
    (
     product_dim.product_type  =  'Brew'
     AND
     purchase_fact.state  =  'New'
    )
    OR
    (
     product_dim.product_type  =  'Service'
     AND
     purchase_fact.state  =  'authorized'
    )
    OR
    (
     product_dim.product_type  =  'Ringtone'
     AND
     purchase_fact.product_id  <>  38196
     AND
     purchase_fact.state  In  ( 'authorized','provisioned','unprovisioned'  )
    )
    OR
    (
     product_dim.product_type  =  'Wallpaper'
     AND
     purchase_fact.product_id  <>  63213
     AND
     purchase_fact.state  In  ( 'authorized','provisioned','unprovisioned'  )
    )
   )
  )
GROUP BY
  product_dim.product_type,
  product_dim.price