Skip to content

Instantly share code, notes, and snippets.

@sqlparser
Created December 12, 2023 08:15
Show Gist options
  • Save sqlparser/4df263446414227dea8991d8acf60bc9 to your computer and use it in GitHub Desktop.
Save sqlparser/4df263446414227dea8991d8acf60bc9 to your computer and use it in GitHub Desktop.
Amazon Redshift Sample SQL
```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;
```
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment