5 files changed, 39 insertions, 0 deletions
diff --git a/graphs/sql/file_archaeology/req01.sql b/graphs/sql/file_archaeology/req01.sql
new file mode 100644
index 0000000..9a9d059
--- /dev/null
+++ b/graphs/sql/file_archaeology/req01.sql
@@ -0,0 +1,2 @@
+SELECT MIN(size) FILTER (WHERE filename ILIKE '%secret%') AS smallest_secret_file_size, MIN(size) AS smallest_file_size
+FROM dtf.madelines_files
diff --git a/graphs/sql/file_archaeology/req02.sql b/graphs/sql/file_archaeology/req02.sql
new file mode 100644
index 0000000..c4cf1d3
--- /dev/null
+++ b/graphs/sql/file_archaeology/req02.sql
@@ -0,0 +1,9 @@
+SELECT
+    filename,
+    size
+FROM dtf.madelines_files
+WHERE
+    size = (SELECT MAX(size) FROM dtf.madelines_files WHERE created_at BETWEEN '2059-12-03 23:59:59'::timestamp - interval '1 week' AND '2059-12-03 23:59:59'::timestamp)
+    AND created_at BETWEEN '2059-12-03 23:59:59'::timestamp
+    - interval '1 week' AND '2059-12-03 23:59:59'::timestamp
+ORDER BY filename
diff --git a/graphs/sql/file_archaeology/req03.sql b/graphs/sql/file_archaeology/req03.sql
new file mode 100644
index 0000000..7e84aa1
--- /dev/null
+++ b/graphs/sql/file_archaeology/req03.sql
@@ -0,0 +1,7 @@
+SELECT
+    filename,
+    size
+FROM dtf.madelines_files
+WHERE
+    size >= (SELECT 0.75 * AVG(size) FROM dtf.madelines_files)
+ORDER BY size DESC,filename
diff --git a/graphs/sql/file_archaeology/req04.sql b/graphs/sql/file_archaeology/req04.sql
new file mode 100644
index 0000000..57d86d3
--- /dev/null
+++ b/graphs/sql/file_archaeology/req04.sql
@@ -0,0 +1,7 @@
+SELECT
+    parent_id AS folder_id,
+    COUNT(filename) FILTER (WHERE permissions LIKE '%__x%') AS nb_executables
+FROM dtf.madelines_files
+GROUP BY parent_id
+HAVING COUNT(filename) FILTER (WHERE permissions LIKE '%__x%') >= 3
+ORDER BY parent_id ASC NULLS FIRST
diff --git a/graphs/sql/file_archaeology/req05.sql b/graphs/sql/file_archaeology/req05.sql
new file mode 100644
index 0000000..a53e4de
--- /dev/null
+++ b/graphs/sql/file_archaeology/req05.sql
@@ -0,0 +1,14 @@
+SELECT
+    owner,
+    COUNT(filename) AS num_files
+FROM dtf.madelines_files
+GROUP BY owner
+HAVING
+    COUNT(filename)
+    = (
+        SELECT MIN(test)
+        FROM
+            (SELECT COUNT(filename) AS test FROM dtf.madelines_files GROUP BY owner
+            ) AS n
+    )
+ORDER BY owner