Skip to content

Instantly share code, notes, and snippets.

View kokosing's full-sized avatar
💤

Grzegorz Kokosiński kokosing

💤
115 followers · 42 following
View GitHub Profile
@kokosing
kokosing / q82_modified.sql
Created March 29, 2016 07:31
*** q82.sql 2016-03-25 14:10:21.910553746 +0100
--- q82_modified.sql 2016-03-25 14:10:21.910553746 +0100
***************
*** 6,16 ****
 "${database}"."${schema}".date_dim,
 "${database}"."${schema}".store_sales
 WHERE i_current_price BETWEEN 44 AND 44+30
 AND inv_item_sk = i_item_sk
 AND d_date_sk=inv_date_sk
! AND d_date BETWEEN cast('1998-06-13' AS date) AND (cast('1998-06-13' AS date) + 60 days)
@kokosing
kokosing / q80_modified.sql
Created March 29, 2016 07:31
*** q80.sql 2016-03-25 14:10:21.910553746 +0100
--- q80_modified.sql 2016-03-25 14:10:21.910553746 +0100
***************
*** 8,18 ****
 AND ss_ticket_number = sr_ticket_number), "${database}"."${schema}".date_dim,
 "${database}"."${schema}".store,
 "${database}"."${schema}".item,
 "${database}"."${schema}".promotion
 WHERE ss_sold_date_sk = d_date_sk
! AND d_date BETWEEN cast('2002-08-05' AS date) AND (cast('2002-08-05' AS date) + 30 days)
@kokosing
kokosing / q77_modified.sql
Created March 29, 2016 07:31
*** q77.sql 2016-03-25 14:10:21.910553746 +0100
--- q77_modified.sql 2016-03-25 14:10:21.910553746 +0100
***************
*** 4,63 ****
 sum(ss_net_profit) AS profit
 FROM "${database}"."${schema}".store_sales,
 "${database}"."${schema}".date_dim,
 "${database}"."${schema}".store
 WHERE ss_sold_date_sk = d_date_sk
! AND d_date BETWEEN cast('2000-08-18' AS date) AND (cast('2000-08-18' AS date) + 30 days)
@kokosing
kokosing / q5_modified.sql
Created March 29, 2016 07:31
*** q5.sql 2016-03-25 14:10:21.906553746 +0100
--- q5_modified.sql 2016-03-25 14:10:21.910553746 +0100
***************
*** 20,30 ****
 sr_net_loss AS net_loss
 FROM "${database}"."${schema}".store_returns) salesreturns,
 "${database}"."${schema}".date_dim,
 "${database}"."${schema}".store
 WHERE date_sk = d_date_sk
! AND d_date BETWEEN cast('1999-08-16' AS date) AND (cast('1999-08-16' AS date) + 14 days)
@kokosing
kokosing / q49_modified.sql
Created March 29, 2016 07:31
*** q49.sql 2016-03-25 14:10:21.906553746 +0100
--- q49_modified.sql 2016-03-25 14:10:21.906553746 +0100
***************
*** 10,20 ****
 rank() over (
 ORDER BY return_ratio) AS return_rank,
 rank() over (
 ORDER BY currency_ratio) AS currency_rank
 FROM
! (SELECT ws.ws_item_sk AS item ,(cast(sum(coalesce(wr.wr_return_quantity,0)) AS dec(15,4))/ cast(sum(coalesce(ws.ws_quantity,0)) AS dec(15,4))) AS return_ratio ,(cast(sum(coalesce(wr.wr_return_amt,0)) AS dec(15,4))/ cast(sum(coalesce(ws.ws_net_paid,0)) AS dec(15,4))) AS currency_ratio
@kokosing
kokosing / q40_modified.sql
Created March 29, 2016 07:31
*** q40.sql 2016-03-25 14:10:21.906553746 +0100
--- q40_modified.sql 2016-03-25 14:10:21.906553746 +0100
***************
*** 9,18 ****
 "${database}"."${schema}".date_dim
 WHERE i_current_price BETWEEN 0.99 AND 1.49
 AND i_item_sk = cs_item_sk
 AND cs_warehouse_sk = w_warehouse_sk
 AND cs_sold_date_sk = d_date_sk
! AND d_date BETWEEN (CAST ('1998-03-31' AS date) - 30 days) AND (CAST ('1998-03-31' AS date) + 30 days)
@kokosing
kokosing / q37_modified.sql
Created March 29, 2016 07:31
*** q37.sql 2016-03-25 14:10:21.906553746 +0100
--- q37_modified.sql 2016-03-25 14:10:21.906553746 +0100
***************
*** 6,16 ****
 "${database}"."${schema}".date_dim,
 "${database}"."${schema}".catalog_sales
 WHERE i_current_price BETWEEN 11 AND 11 + 30
 AND inv_item_sk = i_item_sk
 AND d_date_sk=inv_date_sk
! AND d_date BETWEEN cast('1998-01-05' AS date) AND (cast('1998-01-05' AS date) + 60 days)
@kokosing
kokosing / q32_modified.sql
Created March 29, 2016 07:31
*** q32.sql 2016-03-25 14:10:21.906553746 +0100
--- q32_modified.sql 2016-03-25 14:10:21.906553746 +0100
***************
*** 2,15 ****
 FROM "${database}"."${schema}".catalog_sales,
 "${database}"."${schema}".item,
 "${database}"."${schema}".date_dim
 WHERE i_manufact_id = 284
 AND i_item_sk = cs_item_sk
! AND d_date BETWEEN '2001-01-07' AND (cast('2001-01-07' AS date) + 90 days)
@kokosing
kokosing / q21_modified.sql
Created March 29, 2016 07:31
*** q21.sql 2016-03-25 14:10:21.906553746 +0100
--- q21_modified.sql 2016-03-25 14:10:21.906553746 +0100
***************
*** 10,20 ****
 "${database}"."${schema}".date_dim
 WHERE i_current_price BETWEEN 0.99 AND 1.49
 AND i_item_sk = inv_item_sk
 AND inv_warehouse_sk = w_warehouse_sk
 AND inv_date_sk = d_date_sk
! AND d_date BETWEEN (CAST ('1999-06-23' AS date) - 30 days) AND (CAST ('1999-06-23' AS date) + 30 days)
@kokosing
kokosing / q20_modified.sql
Created March 29, 2016 07:31
*** q20.sql 2016-03-25 14:10:21.906553746 +0100
--- q20_modified.sql 2016-03-25 14:10:21.906553746 +0100
***************
*** 10,20 ****
 WHERE cs_item_sk = i_item_sk
 AND i_category IN ('Women',
 'Jewelry',
 'Sports')
 AND cs_sold_date_sk = d_date_sk
! AND d_date BETWEEN cast('2002-06-20' AS date) AND (cast('2002-06-20' AS date) + 30 days)