How to get the % package_sent_out within a specific time range?

Hi codecademy learners,

I need help with a project of mine. So this project is to calculate the % amount of packages we sent out from 2am-3am the next day, from the total number of packages received between 6am-2am
In truth, I don’t know how to do this.

To better illustrate this, the sample table is as follows:
image

So let’s say, out of packages received on 6th March 2020 from 6am - 7th March 2020 2am from each warehouse location, what’s the % packages we can sent out on the 7th March from 2am - 3am ?

I would very much appreciate the input that I can get. Let me know if there is anything not clear.

Thank you,

Jimmy

Is there a link to this project? It seems like you’d probably need to be using WITH or a window function since you’d need more than one aggregate for the percentage calculation.

Heya @tgrtim ,

Thanks for replying. Unfortunately, there is no link to this project. This project is coming from my current workplace and hence, I created a simulation table above to show what variables are currently involved in this project.

I am hoping with this information, you can still help me to deal with the logic block I am dealing :frowning:
FYI, the formula to calculate the percentage is total number of packages sent out on 7th March 2am-3am divided by the total number of packages we received from 6th March 6am - 7th March 2am. This formula also applies to all other dates. The time is fixed and can’t be changed.

Please let me know if you need further information. I will try my best to provide them

Thanks heaps for this,

Jimmy

Well this community is still a learning environment so simply solving problems for your work isn’t what folks are here for. As I hinted at before the quickest solution that springs to mind is using some form of named sub-query in order to do this calculation.

Forget SQL for now and just work out on paper what values you’d need with basic math in order to obtain this information. Then create one or more queries to obtain that information, once that’s done you can work on cleaning up and improving the query itself.

If you’ve never worked with datetime information before then you’d need to look into how that works with your current implementation of SQL. Start with a basic query to obtain just a small section of the data needed (find packages sent between specific hours for example) and then work out how to combine it with the other necessary data.

I am fully aware that this is a learning community as I am part-time studying in Codecademy too. I am not expecting anyone here to do my work.

I am coming to this community, hoping someone can point me in the right direction.
If you don’t want to help, it’s completely fine either.

I don’t see any harmful reasons why I shouldn’t bring this matter to this community, because I can learn something along the way and at the same time, this is something that other learners can use, in case they encounter the same problem (regardless of whether its from personal project, work, etc).