I have a PostgreSQL query that aggregates data from multiple tables and generates a report. Currently it works perfectly fine and it is quite simple. Here is the query I'm using: SELECT a.name AS account_name, c.name AS campaign_name, ad.name AS…
I want to reduce an array of objects like this: var data = [{ item: "Item 1", pages: 30, chapters: 3 }, { item: "Item 1", pages: 50, chapters: 3 }, { item: "Item 2", pages: 10, chapters: 3 }…
I have a sample data like this : db.contact.insert( [ { _id: 1, name: 'John legend',age: 35}, { _id: 2, name: 'michael Ava',age: 40}, { _id: 3, name: 'Tom holand',age: 20}, { _id: 4, name: 'Frido pex',age: 25}, { _id:…
I'm learning SQL from a website and they have this question for which my attempt below isn't sufficient and needs input. Summary of question: "Find the difference in days between the last post and the first post of the year…
Im trying to make the next query making use of Kotlin SQL Exposed framework. SELECT interation_id, group_id, max(version) FROM interaction GROUP BY group_id; I have a table of interactions, and each interaction group can have multiple interactions, each one marked…
I have two tables similar to this example (simplified for clarity): parent: | id | name | amount | year | ---------------------------------------- | 101 | Henry | 300 | 2020 | ---------------------------------------- | 102 | Carol | 100 | 2020…
See the query below where I get the reputation value from direct addition without a function. SELECT "User"."id", "User"."name", "User"."title", "User"."about", "User"."location", "User"."isModerator", "User"."createdAt", ( -- question vote COALESCE(SUM("PostVote"."value" * 10) FILTER (WHERE "value" = 1 AND "Post"."questionId" IS NULL),…
Working with jsonb and Postgres I'm running into the following problem: My output looks like this: [ {columnName: {}}, { columnName: {} } ] But I would like it to look like this: [ {}, {} ] I want to…
I want to select every pet breed count, grouped by their owner and type. I've written a subquery that returns the breed count by pet type which is almost what i need, except i need the result in a json…
I am working with the ml100k dataset to write a query that can fetch me the most rated movie for every age. Here's how my tables are defined. users id | age | gender | occupation | zipcode ratings userid…