Lavorare con dati personali

È stata aggiunta una nuova tabella al tuo data warehouse, che include informazioni specifiche per utente. Tuttavia, i dati nella colonna personal_info della tabella members sono in formato VARIANT e hanno la forma seguente. Il tuo compito è filtrare e analizzare questi dati per ottenere un set di risultati utile a pianificare offerte speciali per ogni membro.

{
      "age": "56",
      "gender": "Female",
      "name": {
        "first": "Chris",
        "last": "Wilson"
      }
}

Questo esercizio fa parte del corso

Tipi di dati e funzioni in Snowflake

Visualizza il corso

Istruzioni dell'esercizio

Usa la dot-notation per recuperare l'age di un membro dalla colonna personal_info e passa il valore alla funzione TO_NUMBER.
Usa la bracket-notation per recuperare il gender di un membro da personal_info.
Filtra il set di risultati per includere solo i record dei membri con almeno 25 anni.

Esercizio pratico interattivo

Prova a risolvere questo esercizio completando il codice di esempio.

SELECT
    personal_info,
	
    -- Use the TO_NUMBER function to convert the age to a NUMBER
    TO_NUMBER(___:___),
    
    -- Use bracket-notation to retrieve the member's gender
    ___

FROM CORE_GYM.members

-- Only retrieve members who are at least 25
___ TO_NUMBER(personal_info:age) >= ___;

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: Manipulating text data Exercise 2: Managing display length Exercise 3: Transforming text data Exercise 4: Motivating member marketing Exercise 5: Numeric calculations Exercise 6: Comparison operators Exercise 7: Adjusting membership prices Exercise 8: What burns more calories?Exercise 9: Manipulating datetime data Exercise 10: Breaking down a timestamp Exercise 11: How long were you at the gym?Exercise 12: Making appointments Exercise 13: User-defined functions in Snowflake Exercise 14: Creating your own Snowflake function Exercise 15: Ranking gym members by efficiency

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: Appiattire i dati semi-strutturati Exercise 2: Recuperare dati annidati Exercise 3: Lavorare con dati personali

Esercizio in corso

Exercise 4: Creare una descrizione del socio della palestra Exercise 5: Più common table expressions Exercise 6: Individuare i top performer Exercise 7: Confrontare gli allenamenti dei senior Exercise 8: Pivotare i dati Exercise 9: Valutare la durata degli allenamenti Exercise 10: Il CrossFit è un allenamento popolare?Exercise 11: Quali allenamenti bruciano più calorie?Exercise 12: Congratulazioni!