quick_postgres/operations_query_examples.txt at master · burnsie7/quick_postgres · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
----

SELECT "Plans"."title" AS "Title",
       SUM("Plans"."cost") AS "Total sum of Cost"
FROM "public"."plans" AS "Plans"
GROUP BY "Title"
ORDER BY "Title" ASC
LIMIT 1000;

----

SELECT "Plans"."title" AS "Title",
       "Users"."company" AS "Company",
       SUM("Plans"."cost") AS "Total sum of Cost"
FROM "public"."plans" AS "Plans"
INNER JOIN "public"."subscriptions" AS "Subscriptions" ON "Subscriptions"."plan_id" = "Plans"."plan_id"
INNER JOIN "public"."users" AS "Users" ON "Subscriptions"."user_id" = "Users"."user_id"
GROUP BY "Title",
         "Company"
ORDER BY "Title" ASC,
         "Company" ASC
LIMIT 1000;

----

SELECT DATE_TRUNC('day', "Users"."created_date")::DATE AS "Day of Created Date",
       "Users"."company" AS "Company",
       SUM("Plans"."cost") AS "Total sum of Cost"
FROM "public"."users" AS "Users"
INNER JOIN "public"."subscriptions" AS "Subscriptions" ON "Subscriptions"."user_id" = "Users"."user_id"
INNER JOIN "public"."plans" AS "Plans" ON "Subscriptions"."plan_id" = "Plans"."plan_id"
GROUP BY "Day of Created Date",
         "Company"
ORDER BY "Day of Created Date" ASC,
         "Company" ASC
LIMIT 1000;

----

SELECT DATE_TRUNC('day', "Payments"."payment_date")::DATE AS "Payment_Date",
       "Users"."user_id" AS "User Id",
       SUM("Payments"."amount") AS "Total sum of Amount"
FROM "public"."payments" AS "Payments"
INNER JOIN "public"."subscriptions" AS "Subscriptions" ON "Payments"."subscription_id" = "Subscriptions"."subscription_id"
INNER JOIN "public"."users" AS "Users" ON "Subscriptions"."user_id" = "Users"."user_id"
GROUP BY "Payment_Date",
         "User Id"
ORDER BY "Payment_Date" ASC,
         "User Id" ASC
LIMIT 1000;

----

SELECT "Users"."company" AS "Company",
       "Users"."campaign_id" AS "Campaign Id",
       SUM("Payments"."amount") AS "Sum of Amount",
       AVG("Payments"."amount") AS "Average of Amount",
       MAX("Payments"."amount") AS "Maximum Amount",
       MIN("Payments"."amount") AS "Minimum Amount",
       STDDEV("Payments"."amount") AS "Standard Deviation of Amount"
FROM "public"."users" AS "Users"
INNER JOIN "public"."subscriptions" AS "Subscriptions" ON "Subscriptions"."user_id" = "Users"."user_id"
INNER JOIN "public"."payments" AS "Payments" ON "Payments"."subscription_id" = "Subscriptions"."subscription_id"
GROUP BY "Company",
         "Campaign Id"
ORDER BY "Company" ASC,
         "Campaign Id" ASC
LIMIT 1000;

----

SELECT "Users"."company" AS "Company",
       "Users"."zip" AS "Zip",
       "Users"."campaign_id" AS "Campaign Id",
       SUM("Payments"."amount") AS "Amount"
FROM "public"."users" AS "Users"
INNER JOIN "public"."subscriptions" AS "Subscriptions" ON "Subscriptions"."user_id" = "Users"."user_id"
INNER JOIN "public"."payments" AS "Payments" ON "Payments"."subscription_id" = "Subscriptions"."subscription_id"
GROUP BY "Company",
         "Zip",
         "Campaign Id"
ORDER BY "Company" ASC,
         "Zip" ASC,
         "Campaign Id" ASC
LIMIT 1000;

----

SELECT TO_CHAR("Visitors"."created_date", 'IYYY"-W"IW') AS "Week of Created Date",
       "Visitors"."campaign_id" AS "Campaign Id",
       COUNT(DISTINCT "Visitors"."visitor_id") AS "Count of distinct Visitors Visitor Id"
FROM "public"."visitors" AS "Visitors"
GROUP BY "Week of Created Date",
         "Campaign Id"
ORDER BY "Week of Created Date" ASC,
         "Campaign Id" ASC
LIMIT 1000;

---- No Quotes

SELECT Plans.title AS Title, SUM(Plans.cost) AS Total_Cost FROM public.plans AS Plans GROUP BY Title ORDER BY Title ASC LIMIT 1000;

----

SELECT Plans.title AS Title, Users.company AS Company, SUM(Plans.cost) AS Total_Cost FROM public.plans AS Plans INNER JOIN public.subscriptions AS Subscriptions ON Subscriptions.plan_id = Plans.plan_id INNER JOIN public.users AS Users ON Subscriptions.user_id = Users.user_id GROUP BY Title, Company ORDER BY Title ASC, Company ASC LIMIT 1000;

----

SELECT DATE_TRUNC('day', Users.created_date)::DATE AS Created_Date, Users.company AS Company, SUM(Plans.cost) AS Total_Cost FROM public.users AS Users INNER JOIN public.subscriptions AS Subscriptions ON Subscriptions.user_id = Users.user_id INNER JOIN public.plans AS Plans ON Subscriptions.plan_id = Plans.plan_id GROUP BY Created_Date, Company ORDER BY Created_Date ASC, Company ASC LIMIT 1000;

----

SELECT DATE_TRUNC('day', Payments.payment_date)::DATE AS Payment_Date, Users.user_id AS uid, SUM(Payments.amount) AS Total_Amount FROM public.payments AS Payments INNER JOIN public.subscriptions AS Subscriptions ON Payments.subscription_id = Subscriptions.subscription_id INNER JOIN public.users AS Users ON Subscriptions.user_id = Users.user_id GROUP BY Payment_Date, uid ORDER BY Payment_Date ASC, uid ASC LIMIT 1000;

----

SELECT Users.company AS Company, Users.campaign_id AS Campaign_Id, SUM(Payments.amount) AS Sum_of_Amount, AVG(Payments.amount) AS Average_of_Amount, MAX(Payments.amount) AS Maximum_Amount, MIN(Payments.amount) AS Minimum_Amount, STDDEV(Payments.amount) AS Standard_Deviation_of_Amount FROM public.users AS Users INNER JOIN public.subscriptions AS Subscriptions ON Subscriptions.user_id = Users.user_id INNER JOIN public.payments AS Payments ON Payments.subscription_id = Subscriptions.subscription_id GROUP BY Company, Campaign_Id ORDER BY Company ASC, Campaign_Id ASC LIMIT 1000;

----

SELECT Users.company AS Company, Users.zip AS Zip, Users.campaign_id AS Campaign_Id, SUM(Payments.amount) AS Amount FROM public.users AS Users INNER JOIN public.subscriptions AS Subscriptions ON Subscriptions.user_id = Users.user_id INNER JOIN public.payments AS Payments ON Payments.subscription_id = Subscriptions.subscription_id GROUP BY Company, Zip, Campaign_Id ORDER BY Company ASC, Zip ASC, Campaign_Id ASC LIMIT 1000;

----

SELECT TO_CHAR(Visitors.created_date, 'IYYY-WIW') AS Week_of_Visit, Visitors.campaign_id AS Campaign_Id, COUNT(DISTINCT Visitors.visitor_id) AS Count_of_Vistors FROM public.visitors AS Visitors GROUP BY Week_of_Visit, Campaign_Id ORDER BY Week_of_Visit ASC, Campaign_Id ASC LIMIT 1000;


----