I hope you are doing very well.
This is a typical Perl question that is being passed to candidates by recruiters and I cannot yet answer it despite having seen it 3 times. At this point I am determined to solve the problem correctly and grow up because of it. Please give me some hints, just hints as to how best approach the solution.
I am going to post what I know and what I don't know first, and then post the test instructions and the associated DB.pm module, I think this is better:
I know how to use the subroutine from the perl module DB.pm and know that need to connect to a database or to query using dbConnect and query. I have created my own mySQL database from XAMPP with a sample table based on the code. This is a one-column table that gets email addresses and wants to use a Perl program to update another table having count by domain. How do I process and output a count without using complex SQL queries/subroutines (as requested by the test instructions posted below - it says use Perl to process it)? I can use a select * to give me an array of all e-mails, I can go through each record and extract domain so to get an array of domains, then I can sort the array. I think I can go through each record and save it in a temp val (say temp = "yahoo.com"), put this in a hash table (whose key is domain and value is count). In the next pass, I check another record if this is == to temp (so if it is yahoo.com again, update the hashtable with using the key and by incrementing the value - which holds the count. Finally, then I would use another loop and insert statements to translate the hash in to the domain counting table in DB. How correct is this approach?
Another problem I have is that, it says find daily count. since there is no date column in the original table, I am not sure how to just bring up the emails added on a particular day.
For top 50, I just use my sorted hash in descending order and restrict my loop to 50 passes and print the values.
Here is the DB.pmGiven a table 'mailing': CREATE TABLE mailing ( addr VARCHAR(255) NOT NULL ); The mailing table will initially be empty. New addresses will be adde +d on a daily basis. It is expected that the table will store at leas +t 10,000,000 email addresses and 100,000 domains. Write a perl script that updates another table which holds a daily cou +nt of email addresses by their domain name. Use this table to report the top 50 domains by count sorted by percent +age growth of the last 30 days compared to the total. ** NOTE ** - You MUST use the provided DB.pm for all database interaction, and yo +u must use it as it is (DB.pm cannot be modified except for the conne +ction settings). - The original mailing table should not be modified. - All processing must be done in Perl (eg. no complex queries or sub-q +ueries) - Submit a compressed file(tar/zip) with the files required to run you +r script.
package GUI::DB; use strict; use DBI; use vars qw(@ISA @EXPORT); use Exporter; @ISA = qw(Exporter); @EXPORT = qw(dbConnect query); # # dbConnect - connect to the database, get the database handle # sub dbConnect { # Read database settings from config file: my $dsn = "DBI:mysql:database=test"; my $dbh = DBI->connect( $dsn, 'root', '', { RaiseError => 1 } ); return $dbh; } # # query - execute a query with parameters # query($dbh, $sql, @bindValues) # sub query { my $dbh = shift; my $sql = shift; my @bindValues = @_; # 0 or serveral parameters my @returnData = (); # issue query my $sth = $dbh->prepare($sql); if ( @bindValues ) { $sth->execute(@bindValues); } else { $sth->execute(); } if ( $sql =~ m/^select/i ) { while ( my $row = $sth->fetchrow_hashref ) { push @returnData, $row; } } # finish the sql statement $sth->finish(); return @returnData; } __END__
| For: | Use: | ||
| & | & | ||
| < | < | ||
| > | > | ||
| [ | [ | ||
| ] | ] |