Skip to content

Instantly share code, notes, and snippets.

View djouallah's full-sized avatar

Mimoune djouallah

107 followers · 33 following
View GitHub Profile
@djouallah
djouallah / gist:822542e6d043148a293fde380a0cfff9
Created February 22, 2019 07:53
library(readxl)
library(tidyverse)
df1 <- read_excel("/ACTUAL/tracker_Register.xlsx")
df2 <- select(df1,'Row ID','Panel Qty',Accepted, Tracker)
df3 <- gather(df2,category,date,-"Row ID",-"Panel Qty")
df4 <- filter(df3,!is.na(date))
@djouallah
djouallah / index_2.html
Created April 22, 2019 23:34
<!DOCTYPE html>
<html>
<head>
<style>
.vega-actions a {
margin-right: 12px;
color: #757575;
font-weight: normal;
font-size: 13px;
}
@djouallah
djouallah / covid.sql
Created March 26, 2020 12:07
with tablename as(SELECT country_region,date,sum(deaths) as deaths,sum(confirmed) as confirmed FROM `bigquery-public-data.covid19_jhu_csse.summary`
group by 1,2 )
select t.*, deaths - coalesce(lag(deaths) over(partition by country_region order by date),0) as daily_deaths,
confirmed - coalesce(lag(confirmed) over(partition by country_region order by date),0) as daily_confirmed
from tablename t
@djouallah
djouallah / bq
Created July 10, 2020 15:49
with raw as (SELECT
ar.key as key,
ar.value as category,
concat(osm_id,osm_way_id) as Index,geometry,
ST_CENTROID(geometry) AS center_location,
st_x(ST_CENTROID(geometry)) as x,
st_y(ST_CENTROID(geometry)) as y
FROM
`bigquery-public-data.geo_openstreetmap.planet_features`,
UNNEST(all_tags) AS ar
@djouallah
djouallah / group points to polygons
Created July 17, 2020 01:13
WITH Geographies AS
(SELECT ST_GEOGFROMTEXT('POINT(1 1)') AS g ,'newgeopmetry' as t UNION ALL
SELECT ST_GEOGFROMTEXT('POINT(1 3)') AS g ,'newgeopmetry' as t UNION ALL
SELECT ST_GEOGFROMTEXT('POINT(1 2)') AS g ,'newgeopmetry' as t )
SELECT
t, ST_CONVEXHULL(g) AS polygon FROM Geographies group by 1;
@djouallah
djouallah / parameter query
Last active August 31, 2020 23:21
with tt as (SELECT gg.countries_and_territories as Countries , date, sum( daily_deaths ) as daily_deaths ,max(pop) as pop FROM `GIS.covid19new` gg
left join (SELECT countries_and_territories,max( pop_data_2019) as pop FROM `GIS.covid19new` group by 1) pp
on gg.countries_and_territories =pp.countries_and_territories
where gg.countries_and_territories in unnest(@selection) group by 1,2
UNION ALL
SELECT "Rest of the World" as Countries , date,sum( daily_deaths ) as daily_deaths,sum(pop ) FROM `GIS.covid19new` gg
left join (SELECT countries_and_territories,max( pop_data_2019) as pop FROM `GIS.covid19new` group by 1) pp
on gg.countries_and_territories =pp.countries_and_territories
where gg.countries_and_territories not in unnest(@selection) group by 1,2)
@djouallah
djouallah / query plan
Created October 29, 2020 12:07
{
"FileFormatVersion": 1,
"PhysicalQueryPlanRows": [
{
"Records": null,
"Operation": "PartitionIntoGroups: IterPhyOp LogOp=Order IterCols(0, 1)('covid_19_geographic_distribution_worldwide'[countries_and_territories], ''[Sumdaily_confirmed_cases]) #Groups=1 #Rows=212",
"IndentedOperation": "PartitionIntoGroups: IterPhyOp LogOp=Order IterCols(0, 1)('covid_19_geographic_distribution_worldwide'[countries_and_territories], ''[Sumdaily_confirmed_cases]) #Groups=1 #Rows=212",
"Level": 0,
"RowNumber": 1
},
@djouallah
djouallah / BQ Query
Created January 26, 2021 22:11
WITH
xx AS (
SELECT
"australia" AS key,
state,
suburbs,
longitude,
latitude,
IRSAD
FROM
@djouallah
djouallah / bqdirectquery
Created March 4, 2021 23:04
// DAX Query
DEFINE
VAR __DS0FilterTable =
TREATAS({"Coal",
"Renewable",
"Fuel"}, 'UNITARCHIVE'[Technology])
VAR __DS0FilterTable2 =
FILTER(
KEEPFILTERS(VALUES('UNITARCHIVE'[SETTLEMENTDATE])),
@djouallah
djouallah / line and cluster bar
Created January 31, 2022 00:28
{
"$schema": "https://vega.github.io/schema/vega-lite/v5.json",
"usermeta": {
"deneb": {
"build": "1.1.0.20220119#7e76f47",
"metaVersion": 1,
"provider": "vegaLite",
"providerVersion": "5.2.0"
},
"interactivity": {