mysql> SELECT * FROM Students LIMIT 2;
+------------+----------+------------+--------+
| student_id | degree   | entry_year | region |
+------------+----------+------------+--------+
|    1028802 | CS       |       2024 | North  |
|    1278181 | CS+Math  |       2026 | West   |
+------------+----------+------------+--------+
2 rows in set (0.00 sec)

mysql> DESCRIBE Students;
+------------+--------------+------+-----+---------+----------------+

| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+

| student_id | int          | NO   | PRI | NULL    | auto_increment |
| degree     | varchar(100) | YES  |     | NULL    |                |
| entry_year | year         | YES  |     | NULL    |                |
| region     | varchar(50)  | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+

conn = sqlite3.connect(DB_FILE)

students = pd.read_sql("SELECT * FROM Students", conn)
courses = pd.read_sql("SELECT * FROM Courses", conn)
enrolments = pd.read_sql("SELECT * FROM Enrolment", conn)
engagement = pd.read_sql("SELECT * FROM Engagement", conn)

# Joining with Students
analytics = enrolments.merge(students,
    on="student_id",
    how="left"
)

# Join Courses
analytics = analytics.merge(courses,
    on="course_id",
    how="left"
)

engagements = engagements.sort_values(
    ["student_id", "course_id", "event_time"]
)

event_summary = (engagements
    .groupby(
        ["student_id", "course_id"]
    )
    .agg(
        event_count=("event_id", "count"),
        total_duration=("duration", "sum"),
        completion_rate=("completed", "mean")
    )
)

student_means = (
    analytics
    .groupby("student_id")["mark"]
    .mean()
)

analytics["student_mean_mark"] = (
    analytics["student_id"]
    .map(student_means)
)

def classify_risk(row):

    if pd.isna(row["event_count"]):
        return "High Risk"

    if row["mark"] < 40:
        return "High Risk"

    if row["completion_rate"] < 0.5:
        return "Medium Risk"

    if row["event_count"] < 5:
        return "Medium Risk"

    return "Low Risk"

analytics["risk_band"] = (
    analytics
    .apply(
        classify_risk,
        axis=1
    )
)

Output statistics
----------------------------------------------------------------------
Rows: 149,983
Columns: 24 
Execution time: 16.7 seconds

CREATE TABLE StudentCourseAnalytics AS

WITH event_summary AS (
    SELECT
        student_id,
        course_id,
        COUNT(event_id) AS event_count,
        SUM(duration) AS total_duration,
        AVG(completed) AS completion_rate,
        SUM(
            CASE
                WHEN resource_type = 'video'
                THEN duration
            END
        ) AS video_duration,
        SUM(
            CASE
                WHEN resource_type = 'reading'
                THEN duration
            END
        ) AS reading_duration,
        MAX(event_time) AS last_activity
    FROM LearningEvents
    GROUP BY student_id, course_id
),

enrolment_details AS (
    SELECT
        e.student_id,
        e.course_id,
        e.mark,
        e.engagement_level,
        s.degree,
        s.entry_year,
        s.region,
        c.school,
        c.credits,
        es.event_count,
        es.total_duration,
        es.completion_rate,
        es.video_duration,
        es.reading_duration,
        es.last_activity,
        AVG(e.mark) OVER (
            PARTITION BY e.student_id
        ) AS student_mean_mark,
        AVG(e.mark) OVER (
            PARTITION BY e.course_id
        ) AS course_mean_mark
    FROM Enrolments AS e
    LEFT JOIN Students AS s
        ON s.student_id = e.student_id
    LEFT JOIN Courses AS c
        ON c.course_id = e.course_id
    LEFT JOIN event_summary AS es
        ON es.student_id = e.student_id
       AND es.course_id = e.course_id
),

classified AS (
    SELECT
        *,
        CASE
            WHEN event_count IS NULL THEN 'No Activity'
            WHEN event_count > 50 THEN 'High'
            WHEN event_count > 20 THEN 'Medium'
            ELSE 'Low'
        END AS engagement_band,
        CASE
            WHEN event_count IS NULL THEN 'High Risk'
            WHEN mark < 40 THEN 'High Risk'
            WHEN completion_rate < 0.5 THEN 'Medium Risk'
            WHEN event_count < 5 THEN 'Medium Risk'
            ELSE 'Low Risk'
        END AS risk_band,
        mark > student_mean_mark
            AS above_student_average,
        mark > course_mean_mark
            AS above_course_average,
        CASE
            WHEN last_activity IS NULL THEN 999
            ELSE CAST(
                julianday('2026-09-01')
                - julianday(last_activity)
                AS INTEGER
            )
        END AS days_since_last_activity,
        COALESCE(event_count, 0)
            AS filled_event_count,
        COALESCE(total_duration, 0)
            AS filled_total_duration,
        COALESCE(completion_rate, 0)
            AS filled_completion_rate,
        COALESCE(video_duration, 0)
            AS filled_video_duration,
        COALESCE(reading_duration, 0)
            AS filled_reading_duration
    FROM enrolment_details
),

degree_statistics AS (
    SELECT
        degree,
        AVG(mark) AS degree_mean_mark,
        AVG(filled_event_count) AS degree_mean_events
    FROM classified
    GROUP BY degree
)

SELECT
    classified.student_id,
    classified.course_id,
    classified.mark,
    classified.engagement_level,
    classified.degree,
    classified.entry_year,
    classified.region,
    classified.school,
    classified.credits,
    classified.filled_event_count AS event_count,
    classified.filled_total_duration AS total_duration,
    classified.filled_completion_rate AS completion_rate,
    classified.filled_video_duration AS video_duration,
    classified.last_activity,
    classified.filled_reading_duration AS reading_duration,
    classified.student_mean_mark,
    classified.course_mean_mark,
    classified.engagement_band,
    classified.risk_band,
    classified.above_student_average,
    classified.above_course_average,
    classified.days_since_last_activity,
    degree_statistics.degree_mean_mark,
    degree_statistics.degree_mean_events
FROM classified
LEFT JOIN degree_statistics
    ON degree_statistics.degree = classified.degree;

Rows: 149,983
Columns: 24
Execution time: 4.2 seconds

Database: myinner_db
User: mynonsuperuser
Rows: 149,983
Columns: 24
Execution time: 2.3 seconds

CREATE VIEW EventSummary AS
SELECT
    student_id,
    course_id,
    COUNT(event_id) AS event_count,
    SUM(duration) AS total_duration,
    AVG(completed) AS completion_rate,
    SUM(
        CASE
            WHEN resource_type = 'video' THEN duration
            ELSE 0
        END
    ) AS video_duration,
    SUM(
        CASE
            WHEN resource_type = 'reading' THEN duration
            ELSE 0
        END
    ) AS reading_duration,
    MAX(event_time) AS last_activity
FROM LearningEvents
GROUP BY
    student_id,
    course_id;

CREATE VIEW EnrolmentDetails AS
SELECT
    e.student_id,
    e.course_id,
    e.mark,
    e.engagement_level,
    s.degree,
    s.entry_year,
    s.region,
    c.school,
    c.credits
FROM Enrolments AS e
LEFT JOIN Students AS s
    ON s.student_id = e.student_id
LEFT JOIN Courses AS c
    ON c.course_id = e.course_id;

CREATE VIEW StudentCourseFeatures AS
SELECT
    ed.student_id,
    ed.course_id,
    ed.mark,
    ed.engagement_level,
    ed.degree,
    ed.entry_year,
    ed.region,
    ed.school,
    ed.credits,
    es.event_count,
    es.total_duration,
    es.completion_rate,
    es.video_duration,
    es.reading_duration,
    es.last_activity
FROM EnrolmentDetails AS ed
LEFT JOIN EventSummary AS es
    ON es.student_id = ed.student_id
   AND es.course_id = ed.course_id;

SELECT
  student_id,
  course_id,
  degree,
  mark,
  event_count,
  completion_rate,
  days_since_last_activity
FROM StudentCourseAnalyticsFinal
WHERE risk_band = 'High Risk'
ORDER BY days_since_last_activity DESC
LIMIT 10

CREATE VIEW StudentCourseClassified AS
SELECT
    student_id,
    course_id,
    mark,
    engagement_level,
    degree,
    entry_year,
    region,
    school,
    credits,
    COALESCE(event_count, 0) AS event_count,
    COALESCE(total_duration, 0) AS total_duration,
    COALESCE(completion_rate, 0) AS completion_rate,
    COALESCE(video_duration, 0) AS video_duration,
    COALESCE(reading_duration, 0) AS reading_duration,
    last_activity,
    student_mean_mark,
    course_mean_mark,
    CASE
        WHEN event_count IS NULL THEN 'No Activity'
        WHEN event_count > 50 THEN 'High'
        WHEN event_count > 20 THEN 'Medium'
        ELSE 'Low'
    END AS engagement_band,
    CASE
        WHEN event_count IS NULL THEN 'High Risk'
        WHEN mark < 40 THEN 'High Risk'
        WHEN completion_rate < 0.5 THEN 'Medium Risk'
        WHEN event_count < 5 THEN 'Medium Risk'
        ELSE 'Low Risk'
    END AS risk_band,
    mark > student_mean_mark AS above_student_average,
    mark > course_mean_mark AS above_course_average,
    CASE
        WHEN last_activity IS NULL THEN 999
        ELSE CAST(
            julianday('2026-09-01') - julianday(last_activity)
            AS INTEGER
        )
    END AS days_since_last_activity
FROM StudentCourseAnalytics;

video_summary = (
    events[
        events["resource_type"] == "video"
    ]
    .groupby(
        ["student_id", "course_id"]
    )
)

myinner_db=> SELECT DISTINCT resource_type
               FROM learningevents ;
 resource_type 
---------------
 exercise
 quiz
 reading
 video

CREATE VIEW StudentCourseFeatureDocument AS
SELECT
    student_id,
    course_id,
    mark,

    jsonb_build_object(

        'video',
        jsonb_build_object(
            'duration', video_duration
        ),

        'reading',
        jsonb_build_object(
            'duration', reading_duration
        ),

        'engagement',
        jsonb_build_object(
            'event_count', event_count,
            'completion_rate', completion_rate
        )

    ) AS features

FROM StudentCourseFeatures;

SELECT
    student_id,
    course_id
FROM StudentCourseFeatureDocument
WHERE
    (features->'video'->>'duration')::integer
    > 200;

CREATE VIEW StudentCourseFlattenedFeatures AS
SELECT
    student_id,
    course_id,
    
    (features->'video'->>'duration')::integer
        AS video_duration,

    (features->'reading'->>'duration')::integer
        AS reading_duration,

    (features->'engagement'->>'event_count')::integer
        AS event_count,

    (features->'engagement'->>'completion_rate')::numeric
        AS completion_rate

FROM StudentCourseFeatureDocument;

SELECT Title, Journal_Title
FROM articles
JOIN journals;

EXPLAIN SELECT Title, Journal_Title
FROM articles
JOIN journals 
ON (articles.ISSNs = journals.ISSNs)
ORDER BY Journal_Title;

EXPLAIN SELECT Title, Journal_Title
FROM articles
JOIN journals 
ON (articles.ISSNs = journals.ISSNs);

SELECT Title, Authors, ISSNs, Year
FROM Articles
WHERE ISSNs = '2056-9890' AND Month=2 AND YEAR=2015 ;

pd.read_sql_query("""
SELECT Title, Authors, ISSNs, Year
FROM Articles
WHERE ISSNs=? AND Month=? AND YEAR=? ;
""", db, 
params=['2056-9890', 2, 2015]).head()

CREATE EXTERNAL TABLE raw_stream_events (
  event_id STRING,
  user_id STRING,
  event_type STRING,
  event_timestamp TIMESTAMP
)
ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
LOCATION '/hdfs/streaming/events';

CREATE TABLE processed_events AS
SELECT 
  e.event_id,
  e.user_id,
  e.event_type,
  e.event_timestamp,
  u.region
FROM raw_stream_events e
JOIN user_dim u ON e.user_id = u.user_id
WHERE e.event_timestamp IS NOT NULL;

{ "event_id": 3133, "user_id": "alice81", "event_type": "purchase", … }

spark.read.json("s3n://...")
  .registerTempTable("json")
results = spark.sql(
  """SELECT *
     FROM people
     JOIN json ...""")

results = spark.sql(
  "SELECT * FROM people")
names = results.map(lambda p: p.name)

df.select("device").where("signal > 10")