I have two tables. One with metadata, one with billing data. I need to join those effiently in order to assign metadata to costs. Table 1 (metadata) looks like this: Table 2 (billing data) looks like this: For each combination of year, month, id in Table 2, there is an corresponding ID in Table 1. For each ye…
Tag: sql
All tables consisting of numbers less than a fixed number
I am trying to find out all the tables where table names consist of numbers less than a fixed number 16284961 at the end preceded by an underscore for example LOG_16282961. Sample User_segments table: Expected Output: Query: Using above query, although I am getting my result but additionally it also includes …
How to write a query to add a prefix to a comma separated string in MySQL?
I have a table in a MySQL database with a column id. The id column has comma separated values stored in it in VARCHAR format. I want to add a prefix C to every value in the output of the query. Ex: Answer This all sounds like a bad idea, but in some situations it might be needed. It seems
Pivot data T-SQL
I have the following table: I want to pivot it to the following table: I tried to work with the following example: https://www.sqlshack.com/dynamic-pivot-tables-in-sql-server/ But in this case a SUM operator is mandatory. I don’t want to use the SUM operator for my data. What would be the best approach …
sort with special sort number
I have two tables, sort table and assign table SortID Name 0 A 1 B 2 C 3 D I want assign mission to everyone, start with a special sortID example: start with sortID 2, and change order next week. result as below: Date Week MissionA MissionB MissionC MissionD 2021/1/3 1 C D A B 2021/1/4 1 C D A
psycopg2: syntax error at or near “offset”, datetime object
I am running the following SQL query using Python and psycopg2 and is getting errors. SQL query: Errors: Variables and their values Python Functions: How should i fix this? Answer I solved this issue by converting the values of in the list [‘timestamps’] to str from datetime.
Find rows where sum of columns from multiple rows is greater than X
The best way to explain this question is through the real life example it would solve. I have two tables, Match Overview and Match Attributes Match overview looks like While match attributes looks like Where match_id is a foreign key for id in the match overview table, and attribute id is the integer represen…
Junction table indexing in many-to-many relationship
Let’s say we have a PRODUCT table, ORDER table and a junction table PRODUCT_ORDER. I create a junction table with the following script: Also an index for the PK was created automatically: It is expected that there will be practically only read operations for these tables and I would like to index the ju…
Date formatting from BigQuery to Data Studio
I’m trying to connect Google Analytics data from BigQuery for visualization in Data studio; I can’t get the date formatted in a readable format in data studio. I’ve tried using this reg expression: but i still can’t get it to work. The text format is ‘20210921’ Answer Use p…
Casting Decimal to Currency in AWS Athena
I have a field value(decimal(23, 2)) and I am querying over it like this: sum(value) and as output i get 1200000.32 I am trying to cast it to currency to get $1.200.000,32 like this: How can I get the desired currency type? Answer There is no money type in presto. And Amazon’s version of presto does not…