Cloudflare: Stars and Planets — Data Analysis Interview Q&A (2026)

All Questions

Stars and Planets

Cloudflare 👶 Easy Snowflake Joins Column Aliasing Multi-Table

Beginner Mode

Scenario

You are an astronomer at a space observatory with two tables containing star data and planet data.

Task

Write a Snowflake SQL query that:

Joins {{ ref("planets") }} with {{ ref("stars") }} using the planet's star_id column and the star's id column
Aliases the star columns: name as star_name, color as star_color, type as star_type
Aliases the planet columns: name as planet_name, type as planet_type
Aliases stars.distance as distance_star_earth and planets.distance as distance_planet_star
Returns all 7 output columns

Schema

stars

Column	Type	Description
id	Integer	Unique identifier for the star
name	String	Name of the star
color	String	Color of the star
type	String	Type of the star (Dwarf, Giant, Supergiant, etc.)
distance	Float	Distance from Earth in light years

planets

Column	Type	Description
id	Integer	Unique identifier for the planet
name	String	Name of the planet
star_id	Integer	ID of the star the planet orbits
type	String	Type of the planet (Gas Giant, Terrestrial, etc.)
distance	Float	Distance from its star in Astronomical Units (AU)

Example

stars:

id	name	color	type	distance
1	Alpha	Yellow	Dwarf	4.3
2	Beta	Blue	Giant	12.0
3	Gamma	Red	Supergiant	550.0

planets:

id	name	star_id	type	distance
1	Arion	1	Terrestrial	0.8
2	Brahe	1	Gas Giant	5.2
3	Calypso	2	Terrestrial	1.1
4	Draco	3	Gas Giant	40.0

Expected Output:

star_name	star_color	star_type	planet_name	planet_type	distance_star_earth	distance_planet_star
Alpha	Yellow	Dwarf	Arion	Terrestrial	4.3	0.8
Alpha	Yellow	Dwarf	Brahe	Gas Giant	4.3	5.2
Beta	Blue	Giant	Calypso	Terrestrial	12.0	1.1
Gamma	Red	Supergiant	Draco	Gas Giant	550.0	40.0

Note: Both stars and planets share name, type, and distance columns, so the output renames each to avoid ambiguity (e.g., star_name vs planet_name).

SELECT
    s.name AS star_name,
    s.color AS star_color,
    s.type AS star_type,
    p.name AS planet_name,
    p.type AS planet_type,
    s.distance AS distance_star_earth,
    p.distance AS distance_planet_star
FROM {{ ref("planets") }} p
INNER JOIN {{ ref("stars") }} s
    ON p.star_id = s.id

Explanation

Step 1: Joining the two tables

FROM {{ ref("planets") }} p
INNER JOIN {{ ref("stars") }} s
    ON p.star_id = s.id

An INNER JOIN combines rows from both tables where the join condition is satisfied. Here, each planet's star_id is matched to a star's id. Planets without a matching star (or stars without any planets) are excluded from the result.

Step 2: Aliasing overlapping column names

SELECT
    s.name AS star_name,
    ...
    p.name AS planet_name,
    ...

Both tables share name, type, and distance columns. Table aliases (s for stars, p for planets) resolve the ambiguity, and AS renames each column in the output to clearly indicate its origin.

Step 3: Alternative with a CTE

WITH joined AS (
    SELECT
        s.name AS star_name,
        s.color AS star_color,
        s.type AS star_type,
        p.name AS planet_name,
        p.type AS planet_type,
        s.distance AS distance_star_earth,
        p.distance AS distance_planet_star
    FROM {{ ref("planets") }} p
    INNER JOIN {{ ref("stars") }} s
        ON p.star_id = s.id
)
SELECT * FROM joined

Wrapping the join in a CTE produces identical results and is useful when additional transformation steps follow the join.

Quick Solution

Code Environment

Track

	Question	Difficulty	Company	Access

Need more practice in this area? Explore more questions →