slim-skeleton works in docker

composer install skim-skeleton

composer create-project slim/slim-skeleton leo
cd leo

# base image's document root is at html/
mv public html

Create a docker file in dirctory leo/ and buid image

FROM revenuewire/docker-php-alpine:1.0.3

COPY ./ /var/src

docker image build -t leo_try .

Spin up a container

# with volume, codes changes will be reflected in container
docker run -v ${PWD}:/var/src -p 8080:80 -d leo_try

go to http://localhost:8080

PHP-DI

To install

composer require php-di/php-di

Usage

<?php
require './vendor/autoload.php';

class Speaker
{
    public function say($content)
    {
        echo "want to say:", $content, "\n";
    }
}

class Meeting
{
    private $speaker;

    public function __construct(Speaker $speaker)
    {
        $this->speaker = $speaker;
    }

    public function broadCast($content)
    {
        $this->speaker->say($content);
    }
}

//automatically inject speaker into meeting
$container = new DI\Container();
$meeting = $container->get('Meeting');
$meeting->broadCast("Hell World");

/**
 * the above codes are the same as the following
 * $speaker = new Speaker();
 * $meeting = new Meeting($speaker);
 * $meeting->broadCast("Hello World 2");
 */

PHP Slim

Find Alpine Linux Version

cat /etc/os-release

NAME="Alpine Linux"
ID=alpine
VERSION_ID=3.19.1
PRETTY_NAME="Alpine Linux v3.19"
HOME_URL="https://alpinelinux.org/"
BUG_REPORT_URL="https://gitlab.alpinelinux.org/alpine/aports/-/issues"

Check Apache version in Alpine Linux

httpd -v

Server version: Apache/2.4.56 (Unix)
Server built:   Apr 12 2023 20:59:26

Document root in Alpine Linux

open /etc/apache2/httpd.conf to search for DocumentRoot
httpd.conf:DocumentRoot "/var/src/html" 

Explanation of Docker File

All these lines are related to set up apache. CMD is the command the container executes by default when you launch the built image. There, will run run-http.sh to start server. It is under /usr/local/bin/run-http.sh. Can overwrite the content of run-http.sh

COPY local-run-http.sh /usr/local/bin/run-http.sh

COPY conf/httpd.conf /etc/apache2/httpd.conf
COPY index.php /var/src/html
ADD run-http.sh /usr/local/bin/
RUN chmod +x /usr/local/bin/run-http.sh

EXPOSE 80
CMD ["run-http.sh"]

run-http.sh

#!/bin/sh
# stops the execution of a script if has an erro
set -e

# Apache gets grumpy about PID files pre-existing
rm -f /usr/local/apache2/logs/httpd.pid

#-D FOREGROUND run the web server in the background. Otherwise, the server will start and then it will stop.
exec httpd -DFOREGROUND

Set up Slim 4

./composer.phar require slim/slim:"4.*"
./composer.phar require slim/psr7

How to handle API version in Slim 3

group them into version

//Route.php
 $app->group('/v1', function () use ($app) {
            Controllers\Myapi::initRoute($app);
            });

Request in PHP Slim 4

namespace Psr\Http\Message;
interface ServerRequestInterface extends RequestInterface

namespace Slim\Http;
class ServerRequest implements ServerRequestInterface

namespace Slim\Psr7;
class Request extends Message implements ServerRequestInterface

//from slim\Psr7\Request to Slim\Http\ServerRequest
//Slim\Http\ServerRequest has all methods slim\Psr7\Request has. But Slim\Http\ServerRequest has other method
//Slim\Http\ServerRequest is used by PHP Slim 4
$request = new \Slim\Psr7\Request($method, $uri, $h, $cookies, $serverParams, $stream);
return new \Slim\Http\ServerRequest($request);

//assess body content of a request
$params = $request->getParsedBody();

PHP Slim4 Middleware

<?php
/**
 * Sample PHP Slim 4 middleware
 */

use Psr\Http\Message\ResponseInterface;
use Psr\Http\Message\ServerRequestInterface;
use Psr\Http\Server\RequestHandlerInterface;
use Psr\Http\Server\MiddlewareInterface;

class LoggingMiddleware implements MiddlewareInterface
{
    public function process(ServerRequestInterface $request, RequestHandlerInterface $handler): ResponseInterface
    {
        // Log information about the request
        $method = $request->getMethod();
        $uri = $request->getUri()->__toString();

        fwrite(STDOUT, "\n" . "record by new way middleware. Request: $method $uri\n");

        // Call the next middleware / request handler
        return $handler->handle($request);
    }
}

Access Container Defintion in Controller

It is magic of dependency injection. We give object class name and object is injected!

<?php
use Psr\Container\ContainerInterface;

class Action
{
    public function __construct(ContainerInterface $container)
    {
        $this->di = $container;
        $this->configs = $this->di->get('appConfigs');
        fwrite(STDOUT, "\nconfig:" . var_export($this->configs, true) . "\n\n");
    }
}

Middleware run sequency

Last add and first run

Add Middleware to app

$testM = new \Middlewares\TestM2(self::$app->getContainer());
self::$app->add($testM);

Add container definition in Middleware

//inside middleware
$this->di->set("myprop", "myvalue");

//later in controller $container->get("myprop") to access values

use apcu cache

if(!defined('STDOUT')) define('STDOUT', fopen('php://stdout', 'wb'));
        $cacheKey = "app-configs_hH5XabacqzRk";
        if (apcu_exists($cacheKey)) {
            fwrite(STDOUT, "\ngreat! cache hit\n");
            $val = apcu_fetch($cacheKey);
            fwrite(STDOUT, "\nval:" . $val . "\n");
        } else {
            fwrite(STDOUT, "\n key does not exist. will set");
            apcu_store($cacheKey, "leo");
        }

Get header

//return an array of strings for a given header name
$headerValues = $request->getHeader('Accept');

//return a string seperated by comma
$headerValueString = $request->getHeaderLine('Accept');

Custom domain of AWS API Gateway

Steps to create custom domain for api created by AWS API Gateway

• Create a API in API gateway
• Click custom domain names tab. Then use create button to create a custom domain. Need to provide domain name and select a SSL certificate
• Configure API mappings for that domain name. Here map to the API created in the first step.
• Create Record in Route 53. Can be cname or alias and point to API Gateway domain name The format is something like wgatsbbsbfgsdgfz.execute-api.us-west-1.amazonaws.com
  1. Click on your custom domain to view its details
  2. Locate the API Gateway Domain Name field

The record name in Route 53 needs to be the same as custom domain name of API Gateway.

Shell Script

Write to File

#test.txt file will have content: Hello Word How are you (two lines)
cat << EOF >test.txt
Hello World
How are you
EOF

parameters

//test.sh
!/bin/bash
echo Paramters you pass are $1 and $2

//to call: sh test.sh good bad
//output: Paramters you pass are good and bad

Format Shell Script

shfmt -l -w test.sh

MySQL DDL (Data Definition Language) Operations

DDL Document

Column Operations

PHP Cheating Sheet

USort and Heap

class Solution {

    /**
     * Leetcode 253
     * @param Integer[][] $intervals
     * @return Integer
     */
    function minMeetingRooms($intervals) {
        usort($intervals, function($a, $b) {
            return $a[0] > $b[0];
        });

        $heap = new SplMinHeap();
        $ans = 1;
        $heap->insert($intervals[0][1]);

        for ($i=1; $i<count($intervals); $i++) {
            $top = $heap->top();
            if ($top <= $intervals[$i][0]) {
                $heap->extract();

            } else {
                $ans++;
            }
            $heap->insert($intervals[$i][1]);
        }

        return $ans;
    }
}

__invoke() magic method

The __invoke() method is called when a script tries to call an object as a function.

<?php
class CallableClass
{
    public function __invoke($x)
    {
        var_dump($x);
    }
}
$obj = new CallableClass;

//will do var_dump (int 5)
$obj(5);

//bool true
var_dump(is_callable($obj));

::class

SomeClass::class will return the fully qualified name of SomeClass including the namespace.

use WW\PayAPI\Middlewares\AuthUsers;

//same as $magic = "WW\PayAPI\Middlewares\AuthUsers";
$magic = AuthUsers::class;

List extensions installed

php -m 

GuzzleHttp\Client API Call

<?php
//get.php
//need to install library  ./composer.phar require guzzlehttp/guzzle 7.5.0
//usage: php get.php
require 'vendor/autoload.php';

use GuzzleHttp\Client;

$client = new Client();
$url = 'https://bin-lookup.test-api.io/v1/search/519396****8565';

// Make a GET request
$response = $client->request('GET', $url, [
    'headers' => [
        'Accept' => 'application/json',
        'x-api-key' => 'ksjddnd24Sj65vUhyqqqqwww'
    ],
    // You can pass query parameters as well
    'query' => [
        'param1' => 'value1',
        'param2' => 'value2'
    ]
]);

// Get the response status code
$statusCode = $response->getStatusCode();
echo "Status Code: $statusCode\n";

$body = $response->getBody();

// Convert JSON response to associative array
$data = json_decode($body, true);

// Print the response data
print_r($data);

<?php
//post.php
/**
 * usage: php post.php
 */
require 'vendor/autoload.php';

$client = new \GuzzleHttp\Client();

$url = "http://local-support.myapitest.com/rest/v1/orders/purchase-lookup";

$response = $client->post($url, [
    'headers' => [
        'Content-Type' => 'application/json',
        'x-api-key' => 'whasjsjsjsjjsjjsjsuuuu|4'
    ],
    //post body sent will be json encoded string
    'json' => [
        'email' => 'mytest@testp.com'
    ]
]);

$body = $response->getBody();
$ans = json_decode($body, true);

var_dump($ans);

$statusCode = $response->getStatusCode();
echo "Status Code: $statusCode\n";

ngrok tunnel

./ngrok http -host-header=rewrite local.reeyysy.cc:80

Use config file to run multi tunnels

• find config file location.

  ./ngrok authtoken 23siywfFYojsSVe8ojvO0yTEgf4_7dsx9xsdsfffdwww

• set up config file
• start tunnels

  ./ngrok start --all

Sample config file

bind_tls means https only to save endpoints

authtoken: 23siywfFYojsSVe8ojvO0yTEgf4_7dcnsnsdfskflfl
tunnels:
  webpack:
    proto: http
    addr: 8090
    host_header: rewrite
    domain: webpack.myseieie.ninja
    bind_tls: true
  cartSite:
    proto: http
    addr: 80
    domain: localhost
    host_header: rewrite
    bind_tls: true

React Context

React Context Docs

How to work

• Create a context
• Create a context provider component. Add values in the provider
• Wrap components with the provider component
• For these wrapped components, can use useContext to access values

Create Context Sample Code

export const placeContext = createContext();

//create a function component PlaceContextProvider
const PlaceContextProvider = ({ children }) => {
  const [places, dispatch] = useReducer(placeReducer, []);
  return (
    <placeContext.Provider value={{ places, dispatch }}>
      {children}
    </placeContext.Provider>
  );
};

export default PlaceContextProvider;

Main parts to create a context

1. call createContext()// const placeContext = createContext();
2. Create a context provider component. Set values property when create this component.
3. export the above context provider

Use context provider

import PlaceContextProvider from "./contexts/place";

function App() {
  return (
    <div className="App">
      <PlaceContextProvider>
        <Nav />
        <PlaceList />

        <PlaceForm />
      </PlaceContextProvider>
    </div>
  );
}

export default App;

Main parts to use context provider

1. import context provider
2. wrap components which will use context in the context provider

Use context in a component

import { placeContext } from "../contexts/place";

export default function Nav() {
  const { places } = useContext(placeContext);
  return <h1>You have visited {places.length} places.</h1>;
}

Main parts to use context in a component

1. import context (not context provider)
2. pass the context as paramter of useConext function
3. destruct the result which is values we pass to context provider
4. use these values in the component

Stripe Webhook

Stripe provides with webhooks for event such as checkout.session.completed.

Set up webhook

• log into Stripe
• Click developer tab
• Click webhook tab
• following instruction to create a webhook

See Webhook successful or failed event

• log into Stripe and go to webhook tab (see above)
• click a webhook in the list of webhooks
• click one event log in the list of event
• Should see the details of request and response of the event

Test webhook in local server

• ./ngrok http -host-header=rewrite local.reeyysy.cc:80

• set up webhook using url generated by ngrok
• try to fire an event
• if failed, log into Stripe and find the failed event. Click resend to send event again.

Google Pay

Introduction

Google Pay for the web is built on the Payment Request API, an open web standard candidate that relies on the customer's browser as a secure intermediary for payments.

What is Payment Request API

It is one part of Web Payments standard.

Web Payments is an emerging web standard being developed by the W3C to simplify online payments. Web Payments consist of the following web standards:

• Payment Request API
• Payment Handler API
• Payment Method Identifiers
• Payment Method Manifest

//sample code for Payment Request
const request = new PaymentRequest(paymentMethods, paymentDetails);
request.canMakePayment().then(result => {
  if (result) {
    // This browser supports the specified payment method.
  } else {
    // This browser does NOT support the specified payment method.
  }
}).catch(e => {
  // An exception
});

Standalone page to show Google Pay

Google Pay Tutorial

<html>
<head>
    <script async src="https://pay.google.com/gp/p/js/pay.js" onload="onGooglePayLoaded()"></script>
    <script>
        const tokenizationSpec = {
            type: 'PAYMENT_GATEWAY',
            parameters: {
                gateway: 'example',
                gatewayMerchantId: 'gatewayMerchantId'
            }
        };

        const cardPaymentMethod = {
            type: 'CARD',
            tokenizationSpecification: tokenizationSpec,
            parameters: {
                allowedCardNetworks: ['VISA', 'AMEX'],
                allowedAuthMethods:
                    ['PAN_ONLY', 'CRYPTOGRAM_3DS'],
                billingAddressRequired: true,
                billingAddressParameters: {
                    format: 'FULL',
                    phoneNumberRequired: true
                }
            }
        }

        function onGooglePayLoaded() {
            const googlePayClient = new google.payments.api.PaymentsClient({ environment: "TEST" });

            const clientConfiguration = {
                apiVersion: 2,
                apiVersionMinor: 0,
                allowedPaymentMethods: [cardPaymentMethod]
            };

            googlePayClient.isReadyToPay(clientConfiguration)
                .then(function (response) {
                    console.log(response)
                    if (response.result) {
                        //create button
                        let button = googlePayClient.createButton({
                            buttonColor: 'default',
                            buttonType: 'long',
                            onClick: handleSubmit
                        })

                        document.getElementById('gp').appendChild(button);
                    }
                }).catch(function (err) {
                });

            function handleSubmit() {
                const paymentDataRequest = Object.assign({}, clientConfiguration);
                paymentDataRequest.transactionInfo = {
                    totalPriceStatus: 'FINAL',
                    totalPrice: '123.45',
                    currencyCode: 'USD'
                }

                paymentDataRequest.merchantInfo = {
                    merchantId: '0123456789',
                    merchantName: 'Example Merchant'
                }

                googlePayClient.loadPaymentData(paymentDataRequest)
                    .then(function (paymentData) {
                        console.log("payment data:", paymentData)
                    })
            }
        }

        
    </script>
</head>

<body>
    <p>Try Google Pay</p>
    <p id="gp"></p>
</body>

</html>

Local development

• if it is localhost, Google allows it to be http
• let a gmail to joined into sandbox test group and do not need to provide card when click "Google Pay button"

Join sandbox test group

JSON Web Tokens

JSON Web Tokens are an open, industry standard RFC 7519 method for representing claims securely between two parties.

For JWT token, it consists of three parts:

• header about alg and type
• payload
• signature

PHP codes to generate a JWT

    function createJWT($payload, $secret)
    {
        //this is alg used
        $header = ["typ" => "JWT", "alg" => "HS256"];

        $encodedHead = base64url_encode(json_encode($header));
        $encodedPayLoad = base64url_encode(json_encode($payload));

        //build the first two parts
        $encodedHeaderAndPayload = $encodedHead . '.' . $encodedPayLoad;

        $signature = base64url_encode(hash_hmac('sha256', $encodedHeaderAndPayload, $secret, true));

        //build token
        return $encodedHeaderAndPayload. '.' . $signature;
    }

    function base64url_encode($data)
    {
        return rtrim(strtr(base64_encode($data), '+/', '-_'), '=');
    }

Contents

• Apple Pay and Braintree
• AWS API Gateway Custom Domain
• AWS Questions
• AWS Schedule post to an end point using Amazon EventBridge Rules
• AWS WAF
• Block Chain
• Browscap
• Debug
• Docker
• Environment variable in Docker
• Git Cheating Sheet
• Git Cherry Pick
• GitHub Copilot
• GitHub Copilot Agents
• Github two accounts on one machines
• Go Cheating Sheet
• Google reCAPTCHA
• Google Pay
• JavaScript Bind This
• JavaScript Cheating Sheet (Including Promise)
• JavaScript Node.js Module
• JavaScript Promise
• Java Cheating Sheet
• Jenkins
• JWT
• Linux Commands
• MySQL DDL
• MySQL Tips
• More MySQL Tips
• MySQL Query
• Ngrok
• Ngrok Set Up
• NPM
• OpenAi
• PHP Cheating Sheet
• PHP Composer
• PHP Laravel
• PHP Slim 4
• PHP Storm
• PHPUnit 11
• Postman
• Public Key and Private Key
• Python Cheating Sheet
• React
• React Context
• Regular Expression
• Ruby Cheating Sheet
• Set up localhost
• SFTP Server
• SSH Config
• SSL Certificate
• Stop http to https in Chrome
• Stripe Webhook
• TypeScript
• VS codes
• WSL (Window Subsystem for Linux)

Multiple UpSell

React Hook Form

React Hook Form Tutorial

//sample
const [books, dispatch] = useReducer(bookReducer, [])

useReducer Hook

The idea behind it is the same as reduc. Here is useReducer doc

React Context

Here is react context toturial

• Provider needs to wrap a children when return a provider. Here children has a special meaning of children components
• in App.js, components will be wrapped by context provider
• In the component itself, it can access context in three ways
  • contextType
  • consumer
  • const {isLight, light} = useContext(ThemeContext)

In Context file, we need to export both context and context provider

• export const AuthContext = createContext()
• export default AuthConextProvider (This is a component with AuthContext.Provider)

Environment variables in docker

To view variables

1. Log into container

2. printenv

3. printenv PATH

Other options

//in linux
env

//use echo
echo $PATH

To set variables

//to set
export <NAME>='<value>'

//for example to set value for MYPET
export MYPET='cutedog'

//to confirm
printenv MYPET

Set env variable in aws cloudformation

To inject sensitive data into your containers as environment variables, use the secrets container definition parameter.

• Create parameter in AWS parameter store
• Add it in cloudformation template

//code snippet. ExecutionRoleArn is needed because of Secrets and the role needs read permission to access parameter store
TaskDefinition:
    Type: "AWS::ECS::TaskDefinition"
    Properties:
      Family: !Sub "${AWS::StackName}"
      NetworkMode: bridge
      TaskRoleArn: !FindInMap [BuildEnvironment, !Ref 'EnvironmentType', "role"]
      ExecutionRoleArn: !FindInMap [BuildEnvironment, !Ref "EnvironmentType", "executionRole"]
      ContainerDefinitions:
          Secrets:
            - Name: GOOGLE_DRIVE_CREDENTIALS
              ValueFrom: !Sub "arn:aws:ssm:${AWS::Region}:12345678:parameter/GOOGLE_ACCOUNT-${EnvironmentType}"

Use shell script to check environment variables

#check to see if env variable HOME is set
if [[ -n "${HOME}" ]]; then
    echo "$HOME"
else
    echo "Homeless"
fi

Set environment varaibles in Dockerfile

FROM php:8.3.11RC2-zts-alpine3.20

ENV GCT_PROJECT='fluid-fiber-84848484'
ENV GCT_KEY='AIzaSyCejejsllswororowwppw'

Google reCAPTCHA

Step one: sign up recaptcha in Google.

It is free. Get GOOGLE_RECHAPTCHA_SECRET and GOOGLE_RECHAPTCHA_SITE_KEY

Need to add domain with this recaptcha. Do not need to add sub domain. Can add multiple domains

Step two: client side

<script src="https://www.google.com/recaptcha/api.js" async defer></script>
<div class="g-recaptcha" data-callback="recaptchaCallback" data-sitekey="{$siteKey}"></div>

//after check, may want to hide error. Put codeback code here
var recaptchaCallback = function() {

}

Step three: server side validation

User response can be found in $_POST['g-recaptcha-response']

public static function verifyRecaptcha($userResponse)
{
    $url = "https://www.google.com/recaptcha/api/siteverify";
    $curl = curl_init($url);

    $post = ['secret' => getenv('GOOGLE_RECHAPTCHA_SECRET'), 'response' => $userResponse];
    $post = http_build_query($post);

    curl_setopt($curl, CURLOPT_POSTFIELDS, $post);
    curl_setopt($curl, CURLOPT_RETURNTRANSFER, true);
    curl_setopt($curl, CURLOPT_CUSTOMREQUEST, "POST");

    $response = curl_exec($curl);
    curl_close($curl);

    //see what we get
    $result = json_decode($response, true);

    return $result['success'] ?? false;
}

React component for Google reCAPTCHA v2

npm install --save react-google-recaptcha

Stripe Javascript Cart

create a session

        \Stripe\Stripe::setApiKey($this->getSecret());
        $data = [
            'payment_method_types' => ['card'],
            'line_items' => [[
                'price_data' => [
                    'currency' => $currencyCode,
                    'product_data' => [
                        'name' => $productName,
                    ],
                    'unit_amount' => $amountWithoutDecimal,
                ],
                'quantity' => $quantity,
            ]],
            'metadata' => [
                "order_id" => $orderID,
                'order_type' => $orderType,
                'user_id'=> $userId,
               
            ],
            'client_reference_id' => "{$orderType}_{$orderID}",
            'mode' => 'payment',
            'success_url' => $this->getSuccessUrl(),
            'cancel_url' => $this->getCancelUrl(),
        ];

        try {
            $session = \Stripe\Checkout\Session::create($data);
            return $session;
        } catch (Exception $e) {
            echo $e->getMessage();
        }

Initiate Cart By JavaScript

<script  type="text/javascript" src="https://js.stripe.com/v3/"></script>
const stripeSession = await fetch("/stripe/create-checkout-session.php", {
                method: "POST",
                headers: {
                    "Content-Type": "application/json",
                },
                //info needed to create session
                body: JSON.stringify({
                    price: myPrice,
                    type: "product",
                    .....
                    
                }),
            })

            const data = await stripeSession.json()

            var stripe = Stripe('{$stripePublicKey}');
            stripe.redirectToCheckout({
                sessionId: data.sessionId,
            })

Process After Payment

When set up successful url, we can use the placeholder: session_id={CHECKOUT_SESSION_ID}

$successUrl = "https://example.com?session_id={CHECKOUT_SESSION_ID}";

when we create session, we can remember strip checkout session id in server side. Do compare in the successful url page and we know it is paid. Then we can do what we want for a successful transaction.

Create SSL/TSL Certificate in AWS ACM

cname name and cname value

cname allows to point a hostname to any other hostname. hostname1 maps to hostname2. Here hostname1 is called cname name and hostname2 is called cname value. cname name can not be root domain name. something.mydom.com works, but mydom.com can not be used to be cname name.

Can use dig command to find cname /cname value pair.

dig checkout.mytest.com

Request a certification in AWS ACM

1. request a public certification. Also choose DNS validation
2. • If record is in AWS route 53, AWS will do validation automatically and issued the certificate
   • If record is not in AWS route 53, grab the cname name and cname value provided by the certificate. Create a cname record in your domain registra, and AWS will verify it and issue the certificate

Add certificate to ELB

The default Limit for Certificates per Application Load Balancers is 25

• Select the load balancer.
• On the Listeners and rules tab, choose the text in the Protocol:Port (443) column to open the detail page for the listener.
• On the Certificates tab, choose Add certificate.
• Within the ACM and IAM certificates table, select the certificates to add and choose Include as pending below
• Choose Add pending certificates.

Delete a certificate

1. Got to ACM to find that certificate. Check if it is in use. If it is in use, write down which elbs are using it.
2. If in use, go to elb. click cetificates, and check that certificate. Then click remove.
3. After it is removed from all associated elbs, go back to ACM. Choose the certificate, and click delete

API Access-Control-Allow header

$response = $response->withHeader('Access-Control-Allow-Origin', "*");
$response = $response->withHeader('Access-Control-Allow-Credentials', 'true');
return $response->withHeader('Access-Control-Allow-Headers', 'X-Requested-With,
Content-Type, Accept, Origin, Authorization, X-Authorization-JWT, X-CSRF-Token')
->withHeader('Access-Control-Allow-Methods', 'GET, POST, PUT, DELETE, OPTIONS');

MySQL Query

rank

# see leetcode 2173 https://leetcode.com/problems/longest-winning-streak/description/
select
    player_id,
    result,
    RANK () OVER (
        PARTITION BY player_id,
        result
        ORDER BY
            match_day
    ) as my_rank
from
    Matches
order by
    match_day

generate sequence

# 1 to 100
WITH RECURSIVE seq AS (
    SELECT 1 AS value UNION ALL SELECT value + 1 FROM seq WHERE value < 100
    )

create help tables

with platforms as (
select 'Android' as platform
union
select 'IOS' as platform
union
select 'Web' as platform
),

 activities as (
  select 'Reading' as experiment_name
  union
  select 'Sports' as experiment_name
  union
  select 'Programming' as experiment_name
)

More Queries

#day function converts timestamp to day. 2023-10-11 23:33:33 to 11

select day(created), partner_fid, count(*) from cpa_conversion where created>="2024-9-1" group by day(created), partner_fid;