whatsapp_insights/
whatsapp_insights.rs

1/// WhatsApp Insights Query - QAIL Builder API Example
2/// This demonstrates how to build the complex WhatsApp insights query
3/// using QAIL's programmatic builder API with all the new features:
4/// - COUNT(DISTINCT col)
5/// - COUNT(*) FILTER (WHERE ...)  
6/// - CASE WHEN ... THEN ... END
7/// - Type casting (::float8)
8use qail_core::ast::*;
9use qail_core::transpiler::ToSql;
10
11fn main() {
12    // Build the stats CTE subquery
13    let mut stats_query = Qail::get("whatsapp_messages");
14
15    // COUNT(DISTINCT phone_number) AS total_contacts
16    stats_query.columns.push(Expr::Aggregate {
17        col: "phone_number".to_string(),
18        func: AggregateFunc::Count,
19        distinct: true,
20        filter: None,
21        alias: Some("total_contacts".to_string()),
22    });
23
24    // COUNT(*) AS total_messages
25    stats_query.columns.push(Expr::Aggregate {
26        col: "*".to_string(),
27        func: AggregateFunc::Count,
28        distinct: false,
29        filter: None,
30        alias: Some("total_messages".to_string()),
31    });
32
33    // COUNT(*) FILTER (WHERE direction = 'outbound' AND created_at > NOW() - INTERVAL '24 hours') AS messages_sent_24h
34    stats_query.columns.push(Expr::Aggregate {
35        col: "*".to_string(),
36        func: AggregateFunc::Count,
37        distinct: false,
38        filter: Some(vec![
39            Condition {
40                left: Expr::Named("direction".to_string()),
41                op: Operator::Eq,
42                value: Value::String("outbound".to_string()),
43                is_array_unnest: false,
44            },
45            Condition {
46                left: Expr::Named("created_at".to_string()),
47                op: Operator::Gt,
48                value: Value::Function("NOW() - INTERVAL '24 hours'".to_string()),
49                is_array_unnest: false,
50            },
51        ]),
52        alias: Some("messages_sent_24h".to_string()),
53    });
54
55    // COUNT(*) FILTER (WHERE direction = 'inbound' AND created_at > NOW() - INTERVAL '24 hours') AS messages_received_24h
56    stats_query.columns.push(Expr::Aggregate {
57        col: "*".to_string(),
58        func: AggregateFunc::Count,
59        distinct: false,
60        filter: Some(vec![
61            Condition {
62                left: Expr::Named("direction".to_string()),
63                op: Operator::Eq,
64                value: Value::String("inbound".to_string()),
65                is_array_unnest: false,
66            },
67            Condition {
68                left: Expr::Named("created_at".to_string()),
69                op: Operator::Gt,
70                value: Value::Function("NOW() - INTERVAL '24 hours'".to_string()),
71                is_array_unnest: false,
72            },
73        ]),
74        alias: Some("messages_received_24h".to_string()),
75    });
76
77    // COUNT(*) FILTER (WHERE direction = 'inbound' AND status = 'received') AS unread_messages
78    stats_query.columns.push(Expr::Aggregate {
79        col: "*".to_string(),
80        func: AggregateFunc::Count,
81        distinct: false,
82        filter: Some(vec![
83            Condition {
84                left: Expr::Named("direction".to_string()),
85                op: Operator::Eq,
86                value: Value::String("inbound".to_string()),
87                is_array_unnest: false,
88            },
89            Condition {
90                left: Expr::Named("status".to_string()),
91                op: Operator::Eq,
92                value: Value::String("received".to_string()),
93                is_array_unnest: false,
94            },
95        ]),
96        alias: Some("unread_messages".to_string()),
97    });
98
99    // COUNT(*) FILTER (WHERE direction = 'outbound' AND created_at > NOW() - INTERVAL '24 hours' AND status IN ('delivered', 'read')) AS successful_deliveries_24h
100    stats_query.columns.push(Expr::Aggregate {
101        col: "*".to_string(),
102        func: AggregateFunc::Count,
103        distinct: false,
104        filter: Some(vec![
105            Condition {
106                left: Expr::Named("direction".to_string()),
107                op: Operator::Eq,
108                value: Value::String("outbound".to_string()),
109                is_array_unnest: false,
110            },
111            Condition {
112                left: Expr::Named("created_at".to_string()),
113                op: Operator::Gt,
114                value: Value::Function("NOW() - INTERVAL '24 hours'".to_string()),
115                is_array_unnest: false,
116            },
117            Condition {
118                left: Expr::Named("status".to_string()),
119                op: Operator::In,
120                value: Value::Array(vec![
121                    Value::String("delivered".to_string()),
122                    Value::String("read".to_string()),
123                ]),
124                is_array_unnest: false,
125            },
126        ]),
127        alias: Some("successful_deliveries_24h".to_string()),
128    });
129
130    // Create CTE definition (using new API)
131    let stats_cte = stats_query.to_cte("stats");
132
133    // Build final SELECT from CTE with CASE expression for delivery_rate
134    let mut final_query = Qail::get("stats").with_cte(stats_cte);
135    final_query.columns = vec![
136        Expr::Named("total_contacts".to_string()),
137        Expr::Named("total_messages".to_string()),
138        Expr::Named("messages_sent_24h".to_string()),
139        Expr::Named("messages_received_24h".to_string()),
140        Expr::Named("unread_messages".to_string()),
141        Expr::Case {
142            when_clauses: vec![(
143                Condition {
144                    left: Expr::Named("messages_sent_24h".to_string()),
145                    op: Operator::Gt,
146                    value: Value::Int(0),
147                    is_array_unnest: false,
148                },
149                Box::new(Expr::Binary {
150                    left: Box::new(Expr::Binary {
151                        left: Box::new(Expr::Cast {
152                            expr: Box::new(Expr::Named("successful_deliveries_24h".to_string())),
153                            target_type: "float8".to_string(),
154                            alias: None,
155                        }),
156                        op: BinaryOp::Div,
157                        right: Box::new(Expr::Cast {
158                            expr: Box::new(Expr::Named("messages_sent_24h".to_string())),
159                            target_type: "float8".to_string(),
160                            alias: None,
161                        }),
162                        alias: None,
163                    }),
164                    op: BinaryOp::Mul,
165                    right: Box::new(Expr::Named("100.0".to_string())),
166                    alias: None,
167                }),
168            )],
169            else_value: Some(Box::new(Expr::Named("0.0".to_string()))),
170            alias: Some("delivery_rate_24h".to_string()),
171        },
172    ];
173
174    // Generate SQL
175    let sql = final_query.to_sql();
176    println!("Generated SQL:\n{}", sql);
177}
whatsapp_insights/whatsapp_insights.rs

whatsapp_insights/
whatsapp_insights.rs
