Update SQL_Class_5.txt

shashank-mishra219 · web-flow · commit 7937578779ad · 2022-11-13T13:25:59.000+05:30
diff --git a/SQL_Class_5.txt b/SQL_Class_5.txt
@@ -232,3 +232,49 @@ from daily_sales;
 select *,
       lead(sales_amount, 1) over(order by sales_date) as next_day_sales
 from daily_sales;
+
+# Diff between lead and lag
+select *,
+      lag(sales_amount, 1) over(order by sales_date) as pre_day_sales
+from daily_sales;
+
+select *,
+      lead(sales_amount, 1) over(order by sales_date) as next_day_sales
+from daily_sales;
+
+
+# How to use Frame Clause - Rows BETWEEN
+select * from daily_sales;
+
+select *,
+      sum(sales_amount) over(order by sales_date rows between 1 preceding and 1 following) as prev_plus_next_sales_sum
+from daily_sales;
+
+select *,
+      sum(sales_amount) over(order by sales_date rows between 1 preceding and current row) as prev_plus_next_sales_sum
+from daily_sales;
+
+select *,
+      sum(sales_amount) over(order by sales_date rows between current row and 1 following) as prev_plus_next_sales_sum
+from daily_sales;
+
+select *,
+      sum(sales_amount) over(order by sales_date rows between 2 preceding and 1 following) as prev_plus_next_sales_sum
+from daily_sales;
+
+select *,
+      sum(sales_amount) over(order by sales_date rows between unbounded preceding and current row) as prev_plus_next_sales_sum
+from daily_sales;
+
+select *,
+      sum(sales_amount) over(order by sales_date rows between current row and unbounded following) as prev_plus_next_sales_sum
+from daily_sales;
+
+select *,
+      sum(sales_amount) over(order by sales_date rows between unbounded preceding and unbounded following) as prev_plus_next_sales_sum
+from daily_sales;
+
+# Alternate way to esclude computation of current row
+select *,
+      sum(sales_amount) over(order by sales_date rows between unbounded preceding and unbounded following) - sales_amount as prev_plus_next_sales_sum
+from daily_sales;
