add: graphs et rushs

6 files changed, 56 insertions, 0 deletions

diff --git a/graphs/sql/where_is_waldo/req01.sql b/graphs/sql/where_is_waldo/req01.sql
new file mode 100644
index 0000000..9ab6dfb
--- /dev/null
+++ b/graphs/sql/where_is_waldo/req01.sql
@@ -0,0 +1,8 @@
+SELECT sales.id AS sales_id,product_id,sales.checkout_id,sale_timestamp,cashier_shifts.id AS shift_id,employee_id,start_timestamp AS shift_start_timestamp,end_timestamp AS shift_end_timestamp
+INTO dtf.shifts_and_sales_78
+FROM nexus_stores.sales
+INNER JOIN nexus_stores.cashier_shifts ON sales.checkout_id = cashier_shifts.checkout_id
+AND sales.store_id = 78
+AND start_timestamp = timestamp '2059-12-01 13:00:00'
+AND sale_timestamp >= start_timestamp
+AND sale_timestamp < end_timestamp
diff --git a/graphs/sql/where_is_waldo/req02.sql b/graphs/sql/where_is_waldo/req02.sql
new file mode 100644
index 0000000..d7064a4
--- /dev/null
+++ b/graphs/sql/where_is_waldo/req02.sql
@@ -0,0 +1,6 @@
+SELECT *
+FROM nexus_stores.cashier_shifts AS s
+FULL JOIN nexus_stores.cashier_shifts as c
+ON s.id = c.id
+WHERE s.employee_id IS NOT NULL
+AND s.store_id IS NOT NULL
diff --git a/graphs/sql/where_is_waldo/req03.sql b/graphs/sql/where_is_waldo/req03.sql
new file mode 100644
index 0000000..70a712a
--- /dev/null
+++ b/graphs/sql/where_is_waldo/req03.sql
@@ -0,0 +1,4 @@
+SELECT COUNT(s.*) AS store_id_null_count,COUNT(c.*) AS employee_id_null_count
+FROM (SELECT * FROM nexus_stores.cashier_shifts WHERE store_id IS NULL) AS s
+FULL JOIN (SELECT * FROM nexus_stores.cashier_shifts WHERE employee_id IS NULL) AS c
+ON s.id = c.id
diff --git a/graphs/sql/where_is_waldo/req04.sql b/graphs/sql/where_is_waldo/req04.sql
new file mode 100644
index 0000000..3c83d6f
--- /dev/null
+++ b/graphs/sql/where_is_waldo/req04.sql
@@ -0,0 +1,4 @@
+SELECT s.id,checkout_id,employee_id,s.store_id AS shift_store_id,c.store_id AS checkout_store_id,start_timestamp,end_timestamp
+FROM nexus_stores.cashier_shifts AS s
+INNER JOIN nexus_stores.checkouts AS c
+ON c.id = s.checkout_id
diff --git a/graphs/sql/where_is_waldo/req05.sql b/graphs/sql/where_is_waldo/req05.sql
new file mode 100644
index 0000000..8a03061
--- /dev/null
+++ b/graphs/sql/where_is_waldo/req05.sql
@@ -0,0 +1,14 @@
+INSERT INTO nexus_stores.cashier_shifts
+VALUES
+(
+    30000,
+    '68cdd3ec',
+    NULL,
+    78,
+    timestamp '2059-12-01 13:00:00',
+    timestamp '2059-12-01 15:00:00'
+);
+INSERT INTO nexus_stores.sales
+SELECT 325000, id AS product_id,'68cdd3ec',78,timestamp '2059-12-01 14:47:07'
+FROM nexus_stores.products
+WHERE name = 'Golden Apple';
diff --git a/graphs/sql/where_is_waldo/req06.sql b/graphs/sql/where_is_waldo/req06.sql
new file mode 100644
index 0000000..8a7a35e
--- /dev/null
+++ b/graphs/sql/where_is_waldo/req06.sql
@@ -0,0 +1,20 @@
+SELECT
+    e.id AS employee_id,
+    person_id,
+    first_name,
+    last_name
+FROM nexus_stores.employees AS e
+INNER JOIN public.people AS p
+    ON e.person_id = p.id
+WHERE
+    e.id NOT IN (
+        SELECT employee_id
+        FROM nexus_stores.cashier_shifts
+        INNER JOIN nexus_stores.employees
+            ON employee_id = employees.id AND position = 'CASHIER'
+        WHERE
+            start_timestamp = timestamp '2059-12-01 13:00:00'
+            AND cashier_shifts.store_id = 78
+    )
+    AND e.position = 'CASHIER'
+    AND e.store_id = 78