Select data where sum for last 7 from max-date is greater than x

I have a data set as such:

Date          Value    Type
2020-06-01    103      B
2020-06-01    100      A
2020-06-01    133      A
2020-06-11    150      A
2020-07-01    1000     A
2020-07-21    104      A
2020-07-25    140      A
2020-07-28    1600     A
2020-08-01    100      A

Like this:

Type    ISHIGH
A       1
B       0

Here’s the query i tried,

select  type, case when sum(value) > 10 then 1 else 0 end as total_usage
from table_a
where (select sum(value) as usage from tableA where date = max(date)-7)
group by type, date

This is clearly not right. What is a simple way to do this?

Answer

Find the max date by type. Then used it to find last 7 days and sum() the value.

with
cte as
(
    select [type], max([Date]) as MaxDate
    from   tableA
    group by [type]
)
select c.[type], sum(a.Value),
       case when SUM(a.Value) > 1000 then 1 else 0 end as ISHIGH
from   cte c
       inner join tableA a on  a.[type]  = c.[type]
                           and a.[Date] >= DATEADD(DAY, -7, c.MaxDate)
group by c.[type]