title: How to calculate revenue from last 12 months group by age
body: <p>Using the <code>FACT_SALES</code> and <code>DIM_SELLERS</code> tables defined below, I want a query that returns only the seller within each age cohort who has the highest trailing twelve months revenue (&lt;30 days, 31-90 days, 91-120 days, >120 days).</p>
  
  <pre><code>Age = elapsed time to - date since seller start day
  </code></pre>
  
  <p>Table FACT_SALES</p>
  
  <pre><code>dim_seller_sk BIGINT,
  sale_day DATE,
  net_units INT,
  total_sales_revenue FLOAT
  </code></pre>
  
  <p>Table DIM_SELLERS</p>
  
  <pre><code>dim_seller_sk BIGINT,
  seller_name VARCHAR(250),
  seller_start_day DATE`
  </code></pre>
  
  <p>I am trying to get the age like this : </p>
  
  <pre><code>WITH ages AS
  (
      SELECT
          seller_name,
          ROUND(DATEDIFF(day, GETDATE(), seller_start_day), 0) AS age
      FROM 
          dbo.dim_sellers
  ),
  cte2 AS
  (
      SELECT
          ages.seller_name,
          CASE
             WHEN ages.age &lt;= 30 THEN '&lt;= 30 days'
             WHEN ages.age &gt;= 31 AND ages.age &lt;= 90 THEN '31-90 days'
             WHEN ages.age &gt;= 91 AND ages.age &lt;= 120 THEN '91-120 days'
             WHEN ages.age &gt;= 120 THEN '&gt; 120 days'
          END AS age_category
      FROM 
          ages
  )
  SELECT
      'revenue logic',
      cte2.seller_name,
      cte2.age_category
  FROM
      dbo.FACT_SALES, cte2
  GROUP BY
      cte2.seller_name, cte2.age_category
  </code></pre>
  
  <p>How do I calculate the revenue for this? As I have got the <code>age_category</code>.
  Can I select from table and a cte in single select statement?</p>
  
  <p>Missing part of puzzle is revenue logic. Can I get some hints on how can I achieve that. </p>