Challenge 073 solution, both tasks

3 files changed, 61 insertions, 0 deletions

diff --git a/challenge-073/shawn-wagner/README b/challenge-073/shawn-wagner/README
new file mode 100644
index 0000000000..8133463776
--- /dev/null
+++ b/challenge-073/shawn-wagner/README
@@ -0,0 +1 @@
+Solution by Shawn Wagner
diff --git a/challenge-073/shawn-wagner/perl/ch-1.pl b/challenge-073/shawn-wagner/perl/ch-1.pl
new file mode 100755
index 0000000000..ab7bc90186
--- /dev/null
+++ b/challenge-073/shawn-wagner/perl/ch-1.pl
@@ -0,0 +1,29 @@
+#!/usr/bin/env perl
+use warnings;
+use strict;
+use feature qw/signatures say/;
+no warnings qw/experimental/;
+use DBI;
+
+# This is really just a thin layer of perl over SQLite to solve the
+# problems using SQL window functions.
+
+sub task1 ($A, $S) :prototype(\@$) {
+    my $dbh = DBI->connect("dbi:SQLite:dbname=:memory:","","");
+    $dbh->do("CREATE TABLE data(id INTEGER PRIMARY KEY, val INTEGER)");
+    my $stmt = $dbh->prepare("INSERT INTO data(val) VALUES (?)");
+    $stmt->execute_array({}, $A);
+    $S -= 1;
+    my $len = @$A - $S;
+    my $nums = $dbh->selectall_arrayref(<<EOS);
+SELECT min(val) OVER (ORDER BY id ROWS BETWEEN CURRENT ROW AND $S FOLLOWING)
+FROM data
+ORDER BY id
+LIMIT $len
+EOS
+    say "Task 1:\t", join(", ", map { $_->[0] } @$nums);
+}
+
+my @A = (1, 5, 0, 2, 9, 3, 7, 6, 4, 8);
+my $S = 3;
+task1 @A, $S;
diff --git a/challenge-073/shawn-wagner/perl/ch-2.pl b/challenge-073/shawn-wagner/perl/ch-2.pl
new file mode 100755
index 0000000000..a6386b71fa
--- /dev/null
+++ b/challenge-073/shawn-wagner/perl/ch-2.pl
@@ -0,0 +1,31 @@
+#!/usr/bin/env perl
+use warnings;
+use strict;
+use feature qw/say/;
+use DBI;
+
+# This is really just a thin layer of perl over SQLite to solve the
+# problems using SQL window functions.
+
+sub task2 {
+  my @A = @_;
+  my $dbh = DBI->connect("dbi:SQLite:dbname=:memory:","","");
+  $dbh->do("CREATE TABLE data(id INTEGER PRIMARY KEY, val INTEGER)");
+  my $stmt = $dbh->prepare("INSERT INTO data(val) VALUES (?)");
+  $stmt->execute_array({}, \@A);
+
+  my $nums = $dbh->selectall_arrayref(<<EOS);
+SELECT CASE
+         WHEN min(val) OVER w < val THEN min(val) OVER w
+         ELSE 0
+       END
+FROM data
+WINDOW w AS (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
+ORDER BY id
+EOS
+
+  say "Task 2:\t", join(", ", map { $_->[0] } @$nums);
+}
+
+task2 7, 8, 3, 12, 10;
+task2 4, 6, 5;