Skip to content

Instantly share code, notes, and snippets.

View fithisux's full-sized avatar

Vasileios Anagnostopoulos fithisux

8 followers · 6 following
  • Aegean (Agile Actors)
  • Athens/Greece
View GitHub Profile
@fithisux
fithisux / final_query.sql
Last active January 21, 2026 15:14
Final query
with annotated_data as (
SELECT
*,
to_json(struct(* except (object_id, sample_id))) as xxx,
lag(to_json(struct(* except (object_id, sample_id)))) over tracked_id_timeline as prev_xxx,
row_number() over tracked_id_timeline as rr
FROM
VALUES ('A1', 2, 1, 5), ('A1', 1, 1, 5), ('A2', 3, 3, 7), ('A1', 3, 2, 6) tab
(object_id, sample_id, feature_1, feature_2)
WINDOW tracked_id_timeline as (PARTITION BY object_id ORDER BY sample_id)
@fithisux
fithisux / null_json.md
Last active January 21, 2026 15:16
Null json data
object_id sample_id feature_1 feature_2 xxx
A1 2 null null {}
A2 3 null null {}
@fithisux
fithisux / null_json.sql
Last active January 21, 2026 15:15
Null json
%sql
SELECT
*,
to_json(struct(* except (object_id, sample_id))) as xxx
FROM
VALUES ('A1', 2, null, null), ('A2', 3, null, null) tab(object_id, sample_id, feature_1, feature_2)
@fithisux
fithisux / json_generalization.md
Created January 21, 2026 14:54
Data json generalization
object_id sample_id feature_1 feature_2
A1 1 1 5
A1 3 2 6
A2 3 3 7
@fithisux
fithisux / json_case.sql
Last active January 21, 2026 14:53
Json generalization
%sql
with annotated_data as (
SELECT
*,
to_json(struct(* except (object_id, sample_id))) as xxx,
lag(to_json(struct(* except (object_id, sample_id)))) over tracked_id_timeline as prev_xxx,
row_number() over tracked_id_timeline as rr
FROM
VALUES ('A1', 2, 1, 5), ('A1', 1, 1, 5), ('A2', 3, 3, 7), ('A1', 3, 2, 6) tab
(object_id, sample_id, feature_1, feature_2)
@fithisux
fithisux / jsonized.md
Last active January 21, 2026 14:44
Jsonized data
object_id sample_id feature_1 feature_2 xxx
A1 2 1 5 "{""feature_1"":1,""feature_2"":5}"
A1 1 1 5 "{""feature_1"":1,""feature_2"":5}"
A2 3 3 7 "{""feature_1"":3,""feature_2"":7}"
A1 3 2 6 "{""feature_1"":2,""feature_2"":6}"
@fithisux
fithisux / jsonize.sql
Created January 21, 2026 14:43
Jsonize
SELECT
*,
to_json(struct(* except (object_id, sample_id))) as xxx
FROM
VALUES ('A1', 2, 1, 5), ('A1', 1, 1, 5), ('A2', 3, 3, 7), ('A1', 3, 2, 6) tab
(object_id, sample_id, feature_1, feature_2)
@fithisux
fithisux / proper_data.md
Created January 21, 2026 14:38
Proper detection data
object_id sample_id feature_1 feature_2
A1 1 null null
A1 2 1 5
A1 3 null null
A2 3 3 7
@fithisux
fithisux / proper_detection.sql
Last active January 21, 2026 14:52
Proper detection
%sql
with annotated_data as (
SELECT
object_id,
sample_id,
feature_1,
lag(feature_1) OVER tracked_id_timeline as prev_feature_1,
feature_2,
lag(feature_2) OVER tracked_id_timeline prev_feature_2,
row_number() over tracked_id_timeline as rr
@fithisux
fithisux / bad_detection.md
Created January 21, 2026 14:32
Bad detection
object_id sample_id feature_1 prev_feature_1 feature_2 prev_feature_2 rr
A1 1 null null null null 1
A2 3 3 null 7 null 1