Hello Perl Monks,
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.
Given 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.
Here is the DB.pm
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__
-
Are you posting in the right place? Check out Where do I post X? to know for sure.
-
Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
<code> <a> <b> <big>
<blockquote> <br /> <dd>
<dl> <dt> <em> <font>
<h1> <h2> <h3> <h4>
<h5> <h6> <hr /> <i>
<li> <nbsp> <ol> <p>
<small> <strike> <strong>
<sub> <sup> <table>
<td> <th> <tr> <tt>
<u> <ul>
-
Snippets of code should be wrapped in
<code> tags not
<pre> tags. In fact, <pre>
tags should generally be avoided. If they must
be used, extreme care should be
taken to ensure that their contents do not
have long lines (<70 chars), in order to prevent
horizontal scrolling (and possible janitor
intervention).
-
Want more info? How to link
or How to display code and escape characters
are good places to start.