CODEWITHSUNDEEP
sqlamazon-redshiftaggregation
Pavan Kumar
Pavan Kumar

Reputation: 1881

Difficulty in constructing SQL query for Redshift

I don't have much experience in SQL and I am finding difficult to construct the query for the following problem.

Let's say I have sales per month data as follows and is saved in Redshift

ProductId   Year    Month   Sales
A           2018    1       345
A           2018    2       3425
A           2018    3       56545
A           2018    4       234
A           2018    5       23
B           2018    1       645
B           2018    2       2324
B           2018    3       123
B           2018    4       700
B           2018    5       1410
....
....
....

I want to extract the following information from the above table. The query in plain English looks as follows.

  1. Select the product (group by) where sales are greater than 800 for the month 2018/3. For this query, product "A" data should be returned with the sales value for month 2018/3

  2. Select the product (group by) where the sale of month 2018/5 is 100% greater than 2018/4. Here only product "B" is matched and sales data of both months should be included in the result.

Edit: Expected result added.

For query 1

ProductId   Year    Month   Sales
A           2018    3       56545

For query 2 (Grouped)

ProductId   Increase    Year    Month   Sales
B           101.42 %    2018    5       1410
                        2018    4       700

Upvotes: 0

Views: 78

Answers (2)

John Rotenstein
John Rotenstein

Reputation: 269091

Query 1: Select the product where sales are greater than 800 for the month 2018/3.

SELECT
  productid,
  year,
  month,
  sales
FROM table
WHERE year = 2018
  AND month = 3
  AND sales > 800

Query 2: Select the product where the sale of month 2018/5 is 100% greater than 2018/4.

SELECT
  productid
  a.year as previous_month_year,
  a.month as previous_month,
  a.sales as previous_month_sales,
  b.year as year,
  b.month as month,
  b.sales as sales,
  to_char(100 * b.sales / a.sales,'999D99%') as increase
FROM table a
JOIN table b
WHERE a.year = 2018
  AND b.year = 2018
  AND a.month = 4
  AND b.month = 5
  AND b.sales > a.sales * 2
  AND a.productid = b.productid

I'm not sure what you meant by "group by" in your requirements, since no aggregates are required.

Upvotes: 1

Fahmi
Fahmi

Reputation: 37473

You can try below queries

select * from tablename
where year=2018 and month=3 and and Sales>800

select * from tablename where year=2018 and month in (4,5)
and (case when year=2018 and month=5 then sales end)>(case when year=2018 and month=4 then sales end)*2

Upvotes: 1

Related Questions