Challenge #186: Drilling down into Data Analysis

The solution to last week's challenge can be found here!

This week's challenge comes to us from @Original_Yodies. For those of you who work in the energy sector, and specifically drilling, you'll get some industry-specific practice with this challenge!

You are given data with dates when a rotating component (a "washpipe") is replaced on a giant offshore drill called a "topdrive". Each month, you are also given daily data for the amount of time a rig is circulating and/or drilling.

Find the number of circulating or rotating hourse for each installation of a topdrive washpipe. Additional details and assumptions are provided in the start file.

challenge_186_start_file.yxmd

challenge_186_solution.yxmd

Join

Preparation

Advanced

Data Analysis

Intermediate

Transform

Accepted answers

All comments

jrgo

Spoiler

challenge_186_JGO.yxmd

paulfound

Here is my solution, Looking forward to seeing sleeker solutions. 🙂

Spoiler

challenge_186_pf_solution.yxmd

hlmess

Here's my solution. I approached the Rotation and Circulation Hours in a roundabout way but it doesn't not work.

Spoiler

challenge_186_solution.yxmd

ZenonH

One day I'll be the first to post a solution, but not today.

Spoiler

challenge_186_ZHSOL.yxmd

hbraunius

My solution attached.

challenge_186_solution_hb.yxmd

benakesh

challenge_186_bh.yxmd

T_Willins

My solution.

Spoiler

Workflow 186.JPG

challenge_186_solution.yxmd

BradWerner

Fun! It will be great to see how others approached this!

Spoiler

challenge_186_BradWerner.yxmd

TylerNa

Offshore drill rigs are massive!

Spoiler

WC 186 Oil Rig TN.yxmd

RolandSchubert

Spoiler

challenge_186_solution_rsc.yxmd

patrick_digan

I see I may have over complicated this one....Not the first time that's happened.

Spoiler

challenge_186_start_file.yxmd

LiD

Spoiler

challenge_186_end_file.yxmd

TonyA

Solution attached

Spoiler

At first I was thinking batch macro, but it was easier to just expand it out into one big append. I think this can be tightened up. Looking forward to seeing other solutions.

challenge_186_TonyA.yxmd

mhgarcia

Spoiler

SPOILER:

challenge_186_SOLVED.yxmd

etzele

That was fun!

challenge_186_complete.yxmd

KoenVerhagenReply

challenge_186_solution.yxmd

OllieClarke

I do love a generate rows

Spoiler

challenge_186_OC_solution.yxmd

pasccout

Here is my solution.

challenge_186_my_solution.yxmd

RWvanLeeuwen

Not all data matched the installations, but apparently that data is not of interest here? Anyways, the solution contains a flaw with respect to the installation date minus one day (changes during midnight). So here's my solution:

Spoiler

challenge_186_start_file - RWvL.yxmd

justindavis

Spoiler

challenge_186_solution_justindavis.yxmd

JamesFo

Spoiler

Found this one suprisingly tricky to filter the results down - must be tired! Got there in the end.

Challenge 186.PNG

Thanks for setting this one!

James

challenge_186_JamesFo.yxmd

RichoBsJ

Hi! Here my solution 🙂

challenge_186_richobsj.yxmd

alexandramannerings

I like the number of different solutions! Representing Data Prep U with our solution.

Spoiler

186.Data Prep U Solution.yxmd

JonMcMillin

Thanks for the challenge!

Spoiler

completed_Challenge_186.yxmd

sprodger

Spoiler

challenge_186_solution_SP.yxmd

kat

Spoiler

Challenge #186.yxmd

RoDO

My solution

Spoiler

challenge_186_RODO_Solution.yxmd

Pentacleus

Thanks for a good presentation today on the challenge.

I went back to try to solve, and did. The one thing that I did not like was how the posted answer fabricated "Removed Date" = 7/1/2019 for those rows that did not have a Removed Date. My solution maintains a Removed Date of Empty (null), which I feel is more accurate and might avoid misinterpretation if this was a production query.

This challenge also created a challenge for me, a SQL guy. I am used to being able to join two tables and use functions and Boolean operators in my "group by" statement. That would have made this much simpler. So I had to find a way to split this group by into multiple steps. I got to what I needed though.