Dialer Application Weekly Call Stats | SQL Use Case

Here is SQL test I took during a job interview. First is the description, or you can skip directly to the answer. Description: As part of developing the call history functionality of a dialer application, create a query that returns a list of all contacts, the total duration of their calls, and the number of calls during the work week and weekends. The result should have the following column: full_name | phone | type | workweek | weekend. ...

Using a table of users and their transactions, a SQL query that shows users' first and last transaction | SQL Use Case

I took this SQL test during a job interview. The instructions are (spelling mistake is theirs): “We’d like a data model for quick lookup per user. Create a query that returns 1 row per user containing information on their first purchance and their last.” We have a table that looks like this - the table is called subscriptions which can also be considered transactions. Table "public.subscriptions" Column | Type | Collation | Nullable | Default ----------------+-----------------------------+-----------+----------+--------- transaction_id | character(36) | | not null | purchased_at | timestamp without time zone | | not null | purchase_price | double precision | | not null | user_id | character varying(255) | | not null | The way I did this was with two sub queries. One sub query ranked each user’s transactions. The other sub query counted total transactions per user - use this number to know which transaction was their last. The query with total transactions per user has one row for every user, so in the main query start with that, then join to the ranking query twice, once for the first transaction and once for the last. ...

Convert seconds to HH:MM:SS PostgreSQL

Here’s how to convert a numeric integer field of seconds to HH:MM:SS. Use the INTERVAL PostgreSQL data type. The INTERVAL data type is used to store and manipulate a time period. Example SELECT 120 * interval '1 sec'; Output |120 seconds to HH:MM:SS| |-----------------------| |00:02:00| An example with a table of rows of call records that have the length of call in seconds in a column called duration. select duration , duration * interval '1 sec' seconds_to_hh_mm_ss , duration * interval '1 min' min_to_hh_mm_ss from calls; Output ...

Using RStudio – editing your user level and project level .RProfile file

Using the .Rprofile file in RStudio is a great way to automatically load packages and functions upon startup. If you know of additional ways to use this file, let me know. For now, here are easy ways to edit these files at each level. Very simply, use the usethis::edit_r_profile() command to edit your .RProfile file. It defaults to edit the user level file but you can add scope = c("user", "project") to the argument of the function. For example, if you wanted to edit the project level .RProfile file, use this command: usethis::edit_r_profile(scope = c("project")). ...

How to download (export) your WordPress posts and data from your free WordPress blog

It is real easy. First go to the WP Admin page of your blog. It is the same URL as your blog with /wp-admin/ appended to the end. Then click Tools > Export. You should now be on the “Export Content” screen. Follow the instructions on this screen to get your posts and other data.

R – how to select all rows of a data frame where a condition is met

Here is an example, the data looks like this with 3 columns. > fd_nba_raw Position Nickname Injury.Indicator 1 PG Luka Doncic 2 PF Giannis Antetokounmpo 3 C Karl-Anthony Towns GTD 4 SF Kevin Durant 5 SF LeBron James 6 PF Anthony Davis GTD 7 PG Kyrie Irving O 8 SG CJ McCollum 9 SF Kawhi Leonard 10 PG Damian Lillard GTD To select only those with Injury.Indicator = ‘O’ ...

AB or Hypothesis test comparing the mean of two samples (using R)

To compare means of two samples, we need to perform an independent samples t-test, more commonly called Student’s t-Test. To perform this test, we need to know some statistics about the samples: each sample’s mean (x̄), standard deviation (s), and sample size (n). Here is an example: There is data contained in a table of two columns. Column 1 is called ‘category’ and Column 2 is called ‘length.’ The category column contains two options, a and b, while the length column contains lengths in R type num. We want to find out if the mean length of the a values are different than the mean length of the b values. The data looks something like this. The hypothesis statements look like this: ...

How To Optimize a DFS Lineup using R and linear programming

This is a post explain how to optimize a Daily Fantasy Sports (DFS) lineup using R and associated R packages. This specific post will shows how to optimize a line for a 9 player MLB “classic” FanDuel contest. At the time of this writing (April 12, 2020) FanDuel is only offering Full Roster Sim contests because no live MLB games are being played at this time. Here is a link to the specific FanDuel contest I used when writing this guide. Use my referral code and we each get $10: https://fndl.co/t0u657t ...

Wesleyan School (Norcross, Georgia) Football Schedule & Results 2000

9/1/2000 Home vs Landmark Christian Win 24-0 9/8/2000 Home vs Social Circle Win 28-7 9/15/2000 Away at South Forsyth Win 25-22 9/22/2000 Away at Brookstone Win 16-14 Drew Vlass 2 blocked extra points, 1 blocked field goal and an interception 9/29/2000 Away at Pike County 10/13/2000 Home vs Grayson Win 24-0 Jake Humphrey caught a 49-yard pass from Zach Young to open the scoring and later added a 45-yard run to lead Wesleyan (6-0, No. 8 in A). Grayson never threatened to score, crossing midfield only twice in the game. 10/20/2000 Away at Madison County Loss 18-28 ...

Savannah Music Venues and Bands

I had this as a note on my iPhone and now I am putting it here. Venues El Rocko - https://www.instagram.com/elrockolounge/ Two Tides Brewing - https://www.instagram.com/twotidesbrewing/ Service Brewing Coastal Empire Brewing Victory North - https://www.instagram.com/victorynorthsavannah/ Tybee Post Theater - https://www.instagram.com/tybeepost/ District Live - https://www.instagram.com/districtlivesavannah/ Enmarket Arena Lodge of Sorrows - https://www.instagram.com/lodgeofsorrows/ Local Bands Basically Nancy - https://www.instagram.com/nancybasically/ Bastardane - https://www.instagram.com/_bastardane_/ (may not be local anymore) Al Gore Rhythms - https://www.instagram.com/al.gore.rhythms/ Charlie Fog Band - https://www.instagram.com/charliefogband/ Coastal Empire Records - https://www.instagram.com/coastalempirerecords/