Per quanto tempo sei rimasto in palestra?

Mettiamo che ti interessi sapere per quanto tempo ciascun membro è rimasto in palestra. Questo aiuterebbe te e gli altri team che consumano i dati da Snowflake a capire aspetti come l’efficienza dell’allenamento. Per farlo, dovrai usare una delle potenti funzioni sulle date di Snowflake: buona fortuna!

Questo esercizio fa parte del corso

Tipi di dati e funzioni in Snowflake

Visualizza il corso

Istruzioni dell'esercizio

Nell’istruzione SELECT, recupera il campo user_id, oltre alle colonne di check-in/out.
Usa una funzione di Snowflake per trovare la differenza tra checkin_time e checkout_time, assicurandoti che il valore restituito sia in minuti.

Esercizio pratico interattivo

Prova a risolvere questo esercizio completando il codice di esempio.

SELECT
	
    -- Retrieve the user_id, as well as check in/out times
    ___,
    ___,
    ___,
	
    -- Find the number of minutes each member was at the gym for
    ___(___, ___, ___) AS workout_duration

FROM CORE_GYM.visits;

Modifica ed esegui il codice

Questo esercizio fa parte del corso

Tipi di dati e funzioni in Snowflake

IntermediárioNível de habilidade

4.9+

Inizia il corso gratis

Before you can change the world using Snowflake, you need to know the type of data you'll be working with. You'll start with text data, learning to use it in queries and define columns, plus discover how to retrieve column details from any table. Then you'll master numeric data types and date/time data, understanding precision, scale, and how to convert strings to timestamps.

Exercise 1: Text data types Exercise 2: Is that text data?Exercise 3: Working out in the big city Exercise 4: Defining text data Exercise 5: Numeric data types Exercise 6: Go with the FLOAT Exercise 7: Creating a retail table Exercise 8: Datetime data types Exercise 9: Building datetime values Exercise 10: Which timezone?Exercise 11: When did you check in?

Now that you know your Snowflake data inside-and-out, it's time to manipulate and transform it. You'll begin with text data manipulation using TRIM, SPLIT, and CONCAT, then master numeric operations like arithmetic and summary measures with SUM and AVG. You'll explore timestamp transformations by extracting components like year or hours, calculating intervals between dates, and create your own custom Snowflake functions (UDFs) to implement personalized logic.

Exercise 1: Manipolare i dati testuali Exercise 2: Gestire la lunghezza di visualizzazione Exercise 3: Trasformare i dati testuali Exercise 4: Marketing motivazionale per i soci Exercise 5: Calcoli numerici Exercise 6: Operatori di confronto Exercise 7: Adeguare i prezzi degli abbonamenti Exercise 8: Cosa brucia più calorie?Exercise 9: Manipolare i dati datetime Exercise 10: Scomporre un timestamp Exercise 11: Per quanto tempo sei rimasto in palestra?

Esercizio in corso

Exercise 12: Fissare appuntamenti Exercise 13: Funzioni definite dall’utente in Snowflake Exercise 14: Crea la tua funzione in Snowflake Exercise 15: Classificare gli iscritti in base all’efficienza

In this final chapter, you'll crank the heat up with Snowflake's more advanced functionality. You'll start with VARIANT data types (semi-structured data), learning to parse top-level and nested data using dot-notation and bracket-notation, then master common table expressions (CTEs) by building and using multiple CTEs. Finally, you'll wrap up by creating pivoted tables using the PIVOT keyword to transform data into an easy-to-digest format, ready for reports and analysis.

Exercise 1: Flattening Semi-Structured Data Exercise 2: Retrieving nested data Exercise 3: Working with personal info Exercise 4: Building a gym member description Exercise 5: Multiple common table expressions Exercise 6: Identifying high-performers Exercise 7: Comparing senior workouts Exercise 8: Pivoting Data Exercise 9: Evaluating workout length Exercise 10: Is CrossFit a popular workout?Exercise 11: Which workouts burn the most calories?Exercise 12: Congratulations!