Memotivasi pemasaran anggota

Sebagai bagian dari upaya baru untuk memotivasi anggota gym agar kembali, tim pemasaran ingin membuat notifikasi untuk dibagikan kepada anggota setelah berolahraga. Tugas Anda adalah mengotomatisasi pembuatan pesan ini sehingga dapat dengan cepat diintegrasikan ke dalam aplikasi mereka.

Pesan tersebut seharusnya terlihat seperti ini: On <date> you burned <calories burned> calories via <workout type>.

Latihan ini adalah bagian dari kursus

Tipe Data dan Fungsi di Snowflake

Petunjuk latihan

Gunakan fungsi yang sesuai untuk menggabungkan beberapa nilai teks menjadi satu pesan.
Tambahkan calories_burned dari tabel visits ke dalam pesan.
Sertakan workout_type yang dilakukan anggota selama kunjungannya ke dalam pesan, lalu beri alias hasilnya sebagai message.

Latihan interaktif praktis

Cobalah latihan ini dengan menyelesaikan kode contoh berikut.

SELECT
    v.user_id,
    g.location,
    
    -- Concatenate the following strings to create a sentence
    ___(
        'Congrats! On ', 
        DATE(v.checkin_time), 
        ' you burned ', 
        v.___,  -- Add the calories burned
        ' calories via ', 
        v.___  -- Add the workout type
    ) AS message

FROM CORE_GYM.visits AS v
JOIN CORE_GYM.gyms AS g
    ON v.gym_id = g.gym_id;

Edit dan Jalankan Kode

Latihan ini adalah bagian dari kursus

Tipe Data dan Fungsi di Snowflake

SkillTag.level.intermediateSkillTag.label

4.9+

Mulai Kursus 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: Memanipulasi data teks Exercise 2: Mengelola panjang tampilan Exercise 3: Mengubah data teks Exercise 4: Memotivasi pemasaran anggota

Latihan Saat Ini

Exercise 5: Perhitungan numerik Exercise 6: Operator perbandingan Exercise 7: Menyesuaikan harga keanggotaan Exercise 8: Mana yang membakar lebih banyak kalori?Exercise 9: Memanipulasi data datetime Exercise 10: Memecah sebuah timestamp Exercise 11: Berapa lama Anda berada di gym?Exercise 12: Menjadwalkan janji Exercise 13: Fungsi yang ditentukan pengguna di Snowflake Exercise 14: Membuat fungsi Snowflake Anda sendiri Exercise 15: Memeringkat anggota gym berdasarkan efisiensi

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!