Amazon Redshift - Get week wise sales count by cat

I have daily sales data as below. I am trying to group by sales week wise.

I tried using group by that gives total count for the period, how could I modify the query to obtain an output as shown below:

Expected output:

Last N days,Count,Category

Last 7 days,225,Category_1
Last 14 days,136,Category_2
Last 7 days,172,Category_1
Last 14 days,321,Category_2

Input data:

Date,*Sales*,Category

01-06-2018,10,Category_1
01-06-2018,19,Category_1
03-06-2018,3,Category_1
04-06-2018,13,Category_1
05-06-2018,10,Category_1
06-06-2018,14,Category_1
07-06-2018,20,Category_1
08-06-2018,49,Category_1
09-06-2018,5,Category_1
10-06-2018,4,Category_1 
11-06-2018,20,Category_1
12-06-2018,49,Category_1
13-06-2018,5,Category_1
14-06-2018,4,Category_1
01-06-2018,10,Category_1
01-06-2018,34,Category_2
03-06-2018,22,Category_2
04-06-2018,13,Category_2
05-06-2018,1,Category_2
06-06-2018,9,Category_2
07-06-2018,60,Category_2
08-06-2018,9,Category_2
09-06-2018,35,Category_2
10-06-2018,41,Category_2 
11-06-2018,2,Category_2
12-06-2018,9,Category_2
13-06-2018,35,Category_2
14-06-2018,41,Category_2

Query tried:

select count(*),Category from sales
group by Category;

I am using Amazon redshift DB.

Could anyone help,thanks..

Update as of 06/19:

select Category,
sum(case when date >= dateadd(day, -7, CURRENT_DATE) then count(sales) else 0 end) as count_07,
sum(case when date >= dateadd(day, -14, CURRENT_DATE) then count(sales) else 0 end) as count_14
from sales
group by Category

回答1:

I would put the values in separate columns. Something like this:

select category, 
       sum(case when date >= dateadd(day, -7, CURRENT_DATE) then sales else 0 end) as count_07,
       sum(case when date >= dateadd(day, -14, CURRENT_DATE) then sales else 0 end) as count_14
from sales s
group by category;

回答2:

Looks like you are one DATEADD away from the solution.

For seven days, it should be as follows:

Select Count(*),Category From sales Where Date >= Dateadd(day,-7, CURRENT_DATE)

Group By Category;

Change -7 to -14 to get last 14 days.