Amazon Redshift Sample SQL

gistfile1.txt

```sql
-- redshift sample sql
Create table sales(
 dateid int,
  venuestate char(80),
  venuecity char(40),
 venuename char(100),
catname char(50),
Qtr int,
qtysold int,
  pricepaid int,
Year date
);
create view sales_vw as
select * from public.sales
union all
select * from spectrum.sales
;

insert into category_ident(catgroup,catname,catdesc)
select catgroup,catname,catdesc from category;

UPDATE category 
SET    catdesc = 'Broadway Musical' 
WHERE  category.catid IN (SELECT category.catid 
                          FROM   category 
                                 JOIN event 
                                   ON category.catid = event.catid 
                                 JOIN venue 
                                   ON venue.venueid = event.venueid 
                                 JOIN sales 
                                   ON sales.eventid = event.eventid 
                          WHERE  venuecity = 'New York City' 
                                 AND catname = 'Musicals'); 

update category set catid=100
from event join category cat on event.catid=cat.catid
where cat.catgroup='Concerts';

SELECT qtr, 
       Sum(pricepaid)           AS qtrsales, 
       (SELECT Sum(pricepaid) 
        FROM   sales 
               JOIN date 
                 ON sales.dateid = date.dateid 
        WHERE  qtr = '1' 
               AND year = 2008) AS q1sales 
FROM   sales 
       JOIN date 
         ON sales.dateid = date.dateid 
WHERE  qtr IN( '2', '3' ) 
       AND year = 2008 
GROUP  BY qtr 
ORDER  BY qtr; 


WITH venue_sales 
     AS (SELECT venuename, 
                venuecity, 
                Sum(pricepaid) AS venuename_sales 
         FROM   sales, 
                venue, 
                event 
         WHERE  venue.venueid = event.venueid 
                AND event.eventid = sales.eventid 
         GROUP  BY venuename, 
                   venuecity), 
     top_venues 
     AS (SELECT venuename 
         FROM   venue_sales 
         WHERE  venuename_sales > 800000) 
SELECT venuename, 
       venuecity, 
       venuestate, 
       Sum(qtysold)   AS venue_qty, 
       Sum(pricepaid) AS venue_sales 
FROM   sales, 
       venue, 
       event 
WHERE  venue.venueid = event.venueid 
       AND event.eventid = sales.eventid 
       AND venuename IN(SELECT venuename 
                        FROM   top_venues) 
GROUP  BY venuename, 
          venuecity, 
          venuestate 
ORDER  BY venuename; 
```