Hanukkah of Data 5783

day-1.pl

# https://hanukkah.bluebird.sh/5783/1/

use v5.30.0;
use warnings;

use Data::Table;

my $t = Data::Table::fromCSV("noahs-customers.csv");

my $t_p = $t->match_pattern_hash(
  'length(substr($_{name},index($_{name}, " ")+1)) == 10'
);

for my $i (0..$t_p->nofRow-1) {
    my $name = $t_p->elm($i, "name");
    my $lastname = substr($name, index($name, " ") + 1);
    next if $lastname !~ /^\w{10}$/;
    $lastname = lc $lastname;
    my $secretp = join "", map { phonebutton($_) } split "", $lastname;
    my $phone = ($t_p->elm($i, "phone") =~ s/\-//gr);
    say $phone if $secretp eq $phone;
}

sub phonebutton {
    return 2 if $_[0] =~ /[abc]/;
    return 3 if $_[0] =~ /[def]/;
    return 4 if $_[0] =~ /[ghi]/;
    return 5 if $_[0] =~ /[jkl]/;
    return 6 if $_[0] =~ /[mno]/;
    return 7 if $_[0] =~ /[pqrs]/;
    return 8 if $_[0] =~ /[tuv]/;
    return 9 if $_[0] =~ /[wxyz]/;
}

day-2.pl

# https://hanukkah.bluebird.sh/5783/2/

use v5.30.0;
use warnings;
use List::Util qw/uniq/;

use Data::Table;

my $t = Data::Table::fromCSV("noahs-customers.csv");
my $u = Data::Table::fromCSV("noahs-orders.csv");
my $v = Data::Table::fromCSV("noahs-orders_items.csv");

my $t_p = $t->match_pattern_hash(
  '(substr($_{name},0,1)).(substr($_{name},index($_{name}, " ")+1,1)) eq "JD"'
);

my $r = $t_p->join($u, Data::Table::INNER_JOIN, ['customerid'], ['customerid'])
            ->match_pattern_hash('$_{ordered} =~ /^2017/')
            ->join($v, Data::Table::INNER_JOIN, ['orderid'], ['orderid']);

for my $i (0..$r->nofRow-1) {
    say $r->elm($i, "citystatezip")," ",$r->elm($i, "phone") if $r->elm($i, "sku") eq "HOM8601";
}


### The ZIP CODE would be used in solving the puzzle of Day 3.

day-3.pl

# https://hanukkah.bluebird.sh/5783/3/

use v5.30.0;
use warnings;

use Data::Table;

my $t = Data::Table::fromCSV("noahs-customers.csv");

my $t_p = $t->match_pattern_hash(
    'substr($_{birthdate},0,4) % 12 == 2 
      && (
           (substr($_{birthdate},6,1)==3 && substr($_{birthdate},8,2)>="21") 
              ||
           (substr($_{birthdate},6,1)==4 && substr($_{birthdate},8,2)<="19") 
         )
    ');

for my $i (0..$t_p->nofRow-1) { 
    say $t_p->elm($i, "name")," ",$t_p->elm($i, "phone")," ",$t_p->elm($i, "birthdate") if $t_p->elm($i, "citystatezip") =~ /11420/;
}

# Aries: March 21 to April 19
# Year of Dog: 2018, 2006, 1994, 1982, 1970, 1958, and 1946
# ZIP CODE: from the solution of Day 2

day-4.pl

# https://hanukkah.bluebird.sh/5783/4/

use v5.30.0;
use warnings;

use Data::Table;

my $t = Data::Table::fromCSV("noahs-customers.csv");
my $u = Data::Table::fromCSV("noahs-orders.csv");
my $v = Data::Table::fromCSV("noahs-orders_items.csv");

my $u_p = $u->match_pattern_hash(
              '$_{ordered} =~ /^\d{4}-\d{2}-\d{2} 05:\d{2}:\d{2}/'
            );

say $u_p->nofRow;
my $r = $t->join($u_p, Data::Table::INNER_JOIN, ['customerid'], ['customerid'])
          ->join($v, Data::Table::LEFT_JOIN, ['orderid'], ['orderid']);

for my $i (0..$r->nofRow-1) {
    say $r->elm($i, "name"), " ",$r->elm($i, "phone"), " ", $r->elm($i, "total")
        if $r->elm($i, "sku") =~ /^DLI/;
}

# FAIL TO GET THE ANSWER

Day 5

use v5.30.0;
use warnings;

use Data::Table;
use List::Util qw/sum/;

my $t = Data::Table::fromCSV("noahs-customers.csv");
my $u = Data::Table::fromCSV("noahs-orders.csv");
my $v = Data::Table::fromCSV("noahs-orders_items.csv");

my $t_p = $t->match_pattern_hash(
    '$_{citystatezip} =~ /Queens Village/'
);

say $t_p->nofRow;

my $v_p = $v->match_pattern_hash(
    '$_{sku} =~ /^PET/'
);

my $u_p = $u->subTable([0..$u->nofRow-1], ["orderid", "customerid"]);

my $r = $t_p->join($u_p, Data::Table::INNER_JOIN, ['customerid'], ['customerid'])
            ->join($v_p, Data::Table::LEFT_JOIN, ['orderid'], ['orderid'])
            ->group(['customerid'], ['qty'], [ sum ], ['numofpetfood']);

$r->sort('numofpetfood', Data::Table::NUMBER, Data::Table::DESC);

say  $r->elm(0, "name"), " ", $r->elm(0, "phone"), " ", $r->elm(0, "numofpetfood");
say  $r->elm(1, "name"), " ", $r->elm(1, "phone"), " ", $r->elm(1, "numofpetfood");

Day 6

use v6.30.0;
use warnings;
use Data::Table;
use List::Util qw/sum/;

my $t = Data::Table::fromCSV("noahs-customers.csv");
my $u = Data::Table::fromCSV("noahs-orders.csv");
my $w = Data::Table::fromCSV("noahs-products.csv");
my $v = Data::Table::fromCSV("noahs-orders_items.csv")
        ->join($w, Data::Table::LEFT_JOIN, ['sku'], ['sku']);

$v->addCol(0, "stotal");
$v->addCol(0, "wtotal");

for my $i (0..$v->nofRow-1) {
    $v->setElm($i, "stotal", $v->elm($i, "qty")*$v->elm($i, "unit_price"));
    $v->setElm($i, "wtotal", $v->elm($i, "qty")*$v->elm($i, "wholesale_cost"));
}

my $v_p = $v->group(["orderid"], ["stotal", "wtotal"], [\&sum, \&sum], ["ctotal", "ptotal"]);

my $x = $u->join($v_p, Data::Table::INNER_JOIN, ["orderid"], ["orderid"]);

my $y = new Data::Table([], ["customerid", "diff"]);

$x->each_group(["customerid"],
     sub {
         my $a = shift;
         my $amt = 0;
         for my $i (0..$a->nofRow-1) {
            $amt += $a->elm($i, "ptotal") - $a->elm($i, "ctotal");
         }
         $y->addRow([$a->elm(0, "customerid"), $amt]);
     }
);

my $r = $t->join($y, Data::Table::INNER_JOIN, ["customerid"], ["customerid"]);

$r->sort("diff", Data::Table::NUMBER, Data::Table::DESC);

for my $i (0..6) {
    say $r->elm($i, "name"), " ", $r->elm($i, "phone"), " ", $r->elm($i, "diff")
}

Day 7 Part 1

use v5.30.0;
use warnings;
use Data::Table;

my $t = Data::Table::fromCSV("noahs-customers.csv");
my $u = Data::Table::fromCSV("noahs-orders.csv");
my $u_p = $u->match_pattern_hash(
            '$_{customerid} == 8342'
          );
my $v = Data::Table::fromCSV("noahs-orders_items.csv");
my $w = Data::Table::fromCSV("noahs-products.csv");
my $v_p = $v
          ->join($u_p, Data::Table::RIGHT_JOIN, ["orderid"], ["orderid"])
          ->join($w, Data::Table::INNER_JOIN, ["sku"], ["sku"])
          ->match_pattern_hash(
            '$_{desc} =~ /\(\w+\)/'
          );

for my $i (0..$v_p->nofRow-1) {
    say $v_p->elm($i, "desc"), " ", $v_p->elm($i, "ordered");
}

Day 7 Part 2

my $t = Data::Table::fromCSV("noahs-customers.csv");
my $u = Data::Table::fromCSV("noahs-orders.csv");
my $v = Data::Table::fromCSV("noahs-orders_items.csv");
my $w = Data::Table::fromCSV("noahs-products.csv");

my $u_p = $u->match_pattern_hash(
            '$_{customerid} == 8342'
          );

my $v_p = $v
          ->join($u_p, Data::Table::RIGHT_JOIN, ["orderid"], ["orderid"])
          ->join($w, Data::Table::INNER_JOIN, ["sku"], ["sku"])
          ->match_pattern_hash(
            '$_{desc} =~ /\(\w+\)/'
          );

my $v_q = $v
          ->join($w, Data::Table::INNER_JOIN, ["sku"], ["sku"])
          ->match_pattern_hash(
            '    $_{desc} =~ /Electric Machine/
             || $_{desc} =~ /Handmade Toaster/
             || $_{desc} =~ /Electric Crockpot/
             || $_{desc} =~ /Poster/
             || $_{desc} =~ /Electric Toaster/
             || $_{desc} =~ /Manual Mixer/
             || $_{desc} =~ /Jewelry/ '
           )
           ->join($u, Data::Table::INNER_JOIN, ["orderid"], ["orderid"]);


for my $i (0..$v_p->nofRow-1) {
    my $date = substr($v_p->elm($i, "ordered"), 0, 10);
    my $item = ($v_p->elm($i, "desc") =~ s/ \(\w+\)//gr);
    my $v_sub = $v_q->match_pattern_hash(
                   '$_{desc} =~ /'.$item.'/'
                  . ' && '
                  .'$_{ordered} =~ /'.$date.'/')
                ->join($t, Data::Table::INNER_JOIN, ["customerid"], ["customerid"]);
    if ($v_sub->nofRow > 1) {
        for my $j (0..$v_sub->nofRow-1) {
            say $v_sub->elm($j, "desc")," ",
                $v_sub->elm($j, "name")," ",
                $v_sub->elm($j, "phone");
        }
        say "";
    }
}

Day 8

use v5.30.0;
use warnings;
use Data::Table;
use List::Util qw/uniq/;

my $t = Data::Table::fromCSV("noahs-customers.csv");
my $u = Data::Table::fromCSV("noahs-orders.csv");
my $v = Data::Table::fromCSV("noahs-orders_items.csv");
my $w = Data::Table::fromCSV("noahs-products.csv");

sub dsum {
    return scalar uniq @_;
}

my $v_p = $v->match_pattern_hash(
    '$_{sku} =~ /^COL/'
);

my $u_p = $u->subTable([0..$u->nofRow-1], ["orderid", "customerid"]);

my $r =   $t->join($u_p, Data::Table::INNER_JOIN, ["customerid"], ["customerid"])
            ->join($v_p, Data::Table::LEFT_JOIN, ["orderid"], ["orderid"])
            ->group(["customerid"], ["sku"], [ \&dsum ], ["numofcol"]);

$r->sort("numofcol", Data::Table::NUMBER, Data::Table::DESC);

say  $r->elm(0, "name"), " ", $r->elm(0, "phone"), " ", $r->elm(0, "numofcol");  
say  $r->elm(1, "name"), " ", $r->elm(1, "phone"), " ", $r->elm(1, "numofcol");

Day 4

use v5.30.0;
use warnings;
use Data::Table;

my $t = Data::Table::fromCSV("noahs-customers.csv");
my $u = Data::Table::fromCSV("noahs-orders.csv");
my $v = Data::Table::fromCSV("noahs-orders_items.csv");

my $u_p = $u->match_pattern_hash(
            '$_{shipped} =~ /^\d{4}-\d{2}-\d{2} (\d{2}):\d{2}:\d{2}/
            && $1 < 5'
        );

say $u_p->nofRow;
my $r = $v->join($u_p, Data::Table::LEFT_JOIN, ['orderid'], ['orderid'])
        ->join($t, Data::Table::INNER_JOIN, ['customerid'], ['customerid']);

my @arr;

for my $i (1..$r->nofRow-1) {
    push @arr, $r->elm($i, "phone")
        if $r->elm($i, "sku") =~ /^BKY/;
}

@arr = sort {$a cmp $b} @arr;
say join "\n", @arr;

Perl Data::Table Cookbook

http://web.archive.org/web/20200228133556/https://sites.google.com/site/easydatabase/PerlDataTableCookBook.pdf?attredirects=0