this is the nav!
Workspace
Carolyn Boles/

# Duplicate of DataCamp Live Training - Analyzing NASA Planetary Exploration Budgets in SQL (student)

0
Beta

## .mfe-app-workspace-kj242g{position:absolute;top:-8px;}.mfe-app-workspace-11ezf91{display:inline-block;}.mfe-app-workspace-11ezf91:hover .Anchor__copyLink{visibility:visible;}Analyzing NASA Planetary Exploration Budgets in SQL

For much of the last 60 years, NASA has been at the forefront of exploring our solar system. In this live training, we'll see how much money they spent to do this.

For expensive science projects, not least those funded with public money, the price of conducting research is a huge consideration, so budgeting is important.

Here, we'll use a cleaned up version of the public dataset provided by the The Planetary Society.

### Task 1: What is the total cost of all planetary missions over all time?

A good first step in any budgetary analysis is to determine how much money has been spent in total.

For this, we need the `mission_budgets` table. Each row represents the cost in a fiscal year, of one aspect of a project for one mission. There are 5 columns:

• `mission`: The name of the mission.
• `fiscal_year`: The year, for accounting purposes.
• `cost_type`: Fine-grained aspect of the project, e.g., "Spacecraft".
• `cost_group`: Broader aspect of the project, e.g., "Development/Implementation".
• `cost_MUSD`: Cost in million US dollars.

#### Instructions

• Calculate the total cost of all missions over all time as `total_cost_MUSD`.
Unknown integration
DataFrameavailable as
df
variable
```.mfe-app-workspace-qcdhrn{font-size:13px;line-height:1.5384615384615385;font-family:JetBrainsMonoNL,Menlo,Monaco,'Courier New',monospace;}```SELECT SUM("cost_MUSD") AS total_cost_MUSD
FROM mission_budgets

``````
This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.

Due to inflation, costs in the 1960s are not directly equivalent to those in the 2020s. We need to adjust for inflation in order to get a cost in current currency.

Correction factors are available in the `inflation` table. Each row represents an inflation adjustment for a time period relative to today. ther are two columns.

• `fiscal_year`: The year, for accounting purposes. Note that in 1976, inflation was especially high, so two values are provided. "1976" represents the start of the year, and "1976TQ" represents the third quarter onwards.
• `inflation_adjustment`: Multiply currency values from the past time by this number to get current currency values.

#### Instructions

• Join the `mission_budgets` table to the `inflation` table on the fiscal year.
• Calculate the total cost of all missions over all time, adjusted for inflation as `adjusted_total_cost_MUSD`.
Unknown integration
DataFrameavailable as
df
variable
``````SELECT SUM("cost_MUSD" * inflation_adjustment) AS adjusted_total_cost_MSUD
FROM mission_budgets
LEFT JOIN inflation
USING(fiscal_year)``````
This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.

### Task 3: Which was the most expensive mission?

The biggest, grandest missions make headlines, but at some point, someone always asks questions about how much things cost, and the biggest budgets are the first place people look for cost savings. Knowing which is the most expensive project is an essential task for anyone responsible for a budget.

#### Instructions

• Group the budgets by mission, and calculate the total cost for each mission.
• Get the mission with the highest total cost.
Unknown integration
DataFrameavailable as
Df
variable
``````SELECT SUM("cost_MUSD" * inflation_adjustment) AS adjusted_total_cost_MSUD
FROM mission_budgets
LEFT JOIN inflation
USING(fiscal_year)
Group By mission
``````
This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.

### Task 4: How much was spent each year?

Understanding how budgets change over time is also important. NASA has not been immune to the wax and wane of the US economy over the decades, nor the change in political enthusiasm for planetary exploration. More generally, to understand your current budget, it's usually helpful to know the context of how much budget you or your predecessors had in previous years.

#### Instructions

• Group the budgets by mission, and calculate the inflation adjusted total cost for each mission.
• Save the results as the data frame `total_cost_by_year`.
Unknown integration
DataFrameavailable as
total_cost_by_year
variable
``````SELECT fiscal_year, SUM("cost_MUSD" * inflation_adjustment) AS adjusted_total_cost_MSUD
FROM mission_budgets
LEFT JOIN inflation
USING(fiscal_year)
Group By fiscal_year
Order By fiscal_year
-- Calculate the inflation adjusted total cost per year
``````
This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.

#### Instructions

• Draw a bar plot of total cost vs. year.
• Write down your interpretation of the plot.
``````import plotly.express as px

px.bar(
total_cost_by_year,
x="fiscal_year",
To analyze spending by destination, we'll need to look at the `mission_details` table. Each row represenats one mission. There are 4 columns:
• `mission`: The name of the mission.
• `mission_full_name`: The formal name of the mission.
• `destination`: The place in the solar system that the mission is exploring.
• `program`: The name of the NASA program that the mission is part of.