Write a query to get the following columns: week begin - captain - weekly captain's fulfillment etc..

Question

1.00/5 (4 votes)

See more:

I am trying to write a query to get the Week Begin - Captain - Weekly Captain's Fulfillment - Overall Weekly Fulfillment for all Captains - Overall Fulfillment for all Captains, for all Weeks

but this is the code I reached but it's wrong because the fulfilment number doesn't make sense also I don't know how to show the week days so please any help this is for a job interview.

Fulfillment = Completed rides by captain / Scheduled rides (Scheduled Rides = Completed + Cancelled)

Note : I am using BigQuery

What I have tried:

SELECT
DISTINCT Captain, Date, (completed_stat/all_stat) as fulfilment
FROM
(SELECT DISTINCT Date, Captain, COUNT(RideStatus) AS all_stat
FROM project-348014.swvl.swvl
GROUP BY Captain, Date
) t_cat
CROSS JOIN
(SELECT COUNT(RideStatus) AS Completed_stat
FROM project-348014.swvl.swvl WHERE RideStatus = 'completed'
GROUP BY Captain) t_all;

Posted 11-May-22 3:29am

Omar Ammar

Updated 11-May-22 9:47am

v5

Add a Solution

Comments

Richard Deeming 11-May-22 12:06pm

We can't see your data, we can't access your database, and we have no idea what "doesn't make sense" means to you.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2022-05-11T06:40:00

While we are more than willing to help those that are stuck, that doesn't mean that we are here to do it all for you! We can't do all the work, it wouldn't be at all fair on other applicants for us to do it all for you.

And to be honest, it wouldn't help you either. Why not? Simple: I give you a solution, you get the "face to face" interviewer and the guy on the other side of the desk asks you as simple question like "why did you do it like this (indicating part of the solution)?".
You didn't write it. You don't know how it works. You can't answer cogently.
Basically, you can't do the job you have applied for.

You've wasted your time, the companies' time, and deprived others who perhaps could do the job (or at least are more honest about their ignorance) of the chance of an interview.

Or worse: you get the job, and then they find out you can't do it. Does anyone in the department want you working at the next desk? No chance - they'd end up doing your work as well as their own! So you're out on your ear PDQ and they get to spend a couple more months and another load of money (and trust me, recruiting is a very time and cost intensive process) to look for another chancer.

So we need you to do the work, and we will help you when you get stuck. That doesn't mean we will give you a step by step solution you can hand in!
Start by explaining where you are at the moment, and what the next step in the process is. Then tell us what you have tried to get that next step working, and what happened when you did.

But a much better idea is learn stuff, and apply for jobs you can do.